SELECT prod_name FROM products WHERE prod_name LIKE '1000' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;
If you were to try them both you'd discover that the first returns no data and the second returns one row. Why is this?
LIKE matches an entire column. If the text to be matched existed in the middle of a column value,
LIKE would not find it and the row would not be returned (unless wildcard characters were used).
REGEXP, on the other hand, looks for matches within column values, and so if the text to be matched existed in the middle of a column value,
REGEXP would find it and the row would be returned. This is a very important distinction.
REGEXP be used to match entire column values (so that it functions like
LIKE)? Actually, yes, using the
$ anchors, as will be explained later in this tutorial.
REGEXP BINARY Case Sensitive REGEX
Matches Are Not Case-Sensitive Regular expression matching in MySQL are not case-sensitive either case will be matched. To force case-sensitivity, you can use the
BINARY keyword, as in
WHERE prod_name REGEXP BINARY 'product .000'