PHP/MySQL -> Using wildcards in MySQL LIKE and RLIKE operators

The use of wildcards with LIKE and RLIKE operators can save a lot of effort when you write query that looks for some pattern (regular expression) in character string. The wildcards used with LIKE are:
% substitute for unlimited amount of characters at the BEGINING/END of the string.
_ substitute for one character

Look for Customer names that starts with "A" and fouth char is also "a":
SELECT CustomerID, CustomerName
FROM Customers
WHERE CustomerName LIKE 'A__a%';

The result will be like this:
CustomerIDCustomerName
6Anna Matias
13Aida Yespica

Look for Items with price ending by ".5":
SELECT ItemCode, ItemName, Price FROM Items WHERE Price LIKE '%.5';

The result will be like this:
ItemCodeItemNamePrice
5003502TDK Electronics DVD...12.5
77108Coby DVD-224 Compact...93.5

The wildcards used with RLIKE operator are:
^ signifies BEGINING of the string.
$ signifies END of the string.
[[:<:]] substitute characters in the string BEGINING
[[:>:]] substitute characters in the string END
| means OR

Look for Customer names that have have substring "Aida" or substring "Nass" somewhere:
SELECT CustomerID, CustomerName
FROM Customers
WHERE CustomerName RLIKE 'Aida|Nass';

The result will be like this:
CustomerIDCustomerName
10Erika Nass
13Aida Yespica

Look for Item wich name starts with "Philips" and price have digit in the range of 4-5:
SELECT ItemCode, ItemName, Price
FROM Items
WHERE ItemName RLIKE '^Philips'
AND Price RLIKE '[4-5]';

The result will be like this:
ItemCodeItemNamePrice
5003298Philips DVP5140 Multiformat...257

Regular expression for email address validation:
SELECT CustomerID, email
FROM Customers
WHERE email RLIKE '^[-0-9a-z_\\.]+@[-0-9a-z_\\.]+\\.[a-z]{2,3}$';

The result will be like this:
CustomerIDemail
2krips@morning.tv
4pOliv234@hotmail.com

"[-0-9a-z_\\.]" means any digit or (a-z) character ending with dot.
"\\.[a-z]{2,3}" means any (a-z) character after dot for the length between 2 or 3 characters.


sqlexamples.info