By using Regular expressions we can match data anywhere whereas LIKE command matches the entire value. Try by using these two queries
SELECT 'plus2net.com' LIKE '2n';
Output 0
SELECT 'plus2net.com' REGEXP '2n';
Output 1
The power of regular expression comes with its characteristics and by using them useful search queries can be developed.
Characteristics | Description |
. ( dot ) | Matches any single character |
[..] | Matches any character within the brackets. |
[ab] | matches a or b |
[a-z] | matches any letter |
[0-9] | matches any number |
* | Matches zero or more instances of just previous character |
+ | Matches one or more instances of just previous character |
? | Matches zero or more instances of just previous character |
^ | Start of a string for pattern match |
$ | End of a string for pattern match |
{n} | Repeat n number of times. Example ^.{6}$ |
Matches with 0 or more instances
SELECT * FROM STUDENT WHERE name REGEXP 'b*'
We will get all records
Matches with 1 or more instances
SELECT * FROM STUDENT WHERE name REGEXP 'c+'
id | name | class | mark | sex |
12 | Recky | Six | 94 | female |
20 | Jackly | Nine | 65 | female |
26 | Crelea | Seven | 79 | male |
Matches with 0 or more instances
SELECT * FROM STUDENT WHERE name REGEXP 'c?'
We will get all records
Names starting with a
SELECT * FROM STUDENT WHERE name REGEXP '^a'
id | name | class | mark | sex |
3 | Arnold | Three | 55 | male |
6 | Alex John | Four | 55 | male |
8 | Asruid | Five | 85 | male |
Getting all names starting with a or b
SELECT * FROM STUDENT WHERE name REGEXP '^[ab]'
id | name | class | mark | sex |
3 | Arnold | Three | 55 | male |
6 | Alex John | Four | 55 | male |
8 | Asruid | Five | 85 | male |
10 | Big John | Four | 55 | female |
14 | Bigy | Seven | 88 | female |
21 | Babby John | Four | 69 | female |
27 | Big Nose | Three | 81 | female |
32 | Binn Rott | Seven | 90 | female |
Getting all the records ending with a
SELECT * FROM STUDENT WHERE name REGEXP 'a$'
id | name | class | mark | sex |
26 | Crelea | Seven | 79 | male |
Records with three characters
SELECT * FROM student WHERE name REGEXP '^...$'
id | name | class | mark | sex |
13 | Kty | Seven | 88 | female |
Records with four characters by using {n}
SELECT * FROM student WHERE name REGEXP '^.{4}$'
id | name | class | mark | sex |
14 | Bigy | Seven | 88 | female |
Using NOT with REGEXP
SELECT * FROM STUDENT WHERE name NOT REGEXP '^[abgtrjkm]'
id | name | class | mark | sex |
18 | Honny | Five | 75 | male |
23 | Herod | Eight | 79 | male |
26 | Crelea | Seven | 79 | male |
Case sensitive with BINARY
SELECT * FROM STUDENT WHERE name REGEXP BINARY '^[Ab]'
id | name | class | mark | sex |
3 | Arnold | Three | 55 | male |
6 | Alex John | Four | 55 | male |
8 | Asruid | Five | 85 | male |
Download sql dump of this student table