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
SQL Video Tutorials