MySQL: Search String using Regular Expressions (REGEXP)

A regular expression is a special text or string which is describing a different search pattern.

When we are validating email addresses, IP-Addresses and any other special string, we should use a regular expression for perfect pattern matching.

Using regular expressions, you can check different special characters, numbers, lower and capital letters.

MySQL provides REGEXP for performing pattern matching in WHERE clause.
Some of the few examples are here:

Create a table with sample data:

1 2 3 4 5 6 7 8 9 10

CREATE TABLE tbl_TableText ( TextData TEXT );   INSERT INTO tbl_TableText VALUES ('Anvesh'),('125688'),('dbrnd$*$') ,('dbrnd'),('(!research!)'),('dbrnd/.com') ,('DATABASE');

Check for numbers:

1 2 3 4 5 6 7

SELECT *FROM tbl_TableText WHERE TextData REGEXP '[0-9]' /* Result :   TextData ------------- 125688 */

Check for lower and upper letters:

1 2 3 4 5 6 7 8 9 10 11 12

SELECT *FROM tbl_TableText WHERE TextData REGEXP '[A-Za-z]' /* Result :   TextData ------------- Anvesh dbrnd$*$ dbrnd (!research!) dbrnd/.com DATABASE */

Check for all symbols:

1 2 3 4 5 6 7 8 9

SELECT *FROM tbl_TableText WHERE TextData REGEXP '[$*/.(!)]' /* Result :   TextData ------------- dbrnd$*$ (!research!) dbrnd/.com */

Check for all the combinations:

1 2 3 4 5 6 7 8 9 10 11 12 13

SELECT *FROM tbl_TableText WHERE TextData REGEXP '[a-zA-Z0-9$*/.(!)]' /* Result :   TextData ------------- Anvesh 125688 dbrnd$*$ dbrnd (!research!) dbrnd/.com DATABASE */