MySQL —

LIKE VS. REGEXP

LIKE Versus REGEXP There is one very important difference between LIKE and REGEXP. Look at these two statements:

SELECT prod_name
FROM products
WHERE prod_name LIKE '1000'
ORDER BY prod_name;

and

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.

So can REGEXP be used to match entire column values (so that it functions like LIKE)? Actually, yes, using the ^ and $ 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'