SQL REGEXP query to match pattern in data

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 rcords

Matches with 1 or more instances

SELECT * FROM STUDENT WHERE name REGEXP  'c+'
idnameclassmarksex
12 ReckySix94female
20 JacklyNine65female
26 CreleaSeven79male

Matches with 0 or more instances

SELECT * FROM STUDENT WHERE name REGEXP  'c?'
We will get all rcords

Names starting with a

SELECT * FROM STUDENT WHERE name REGEXP  '^a'
idnameclassmarksex
3ArnoldThree55male
6Alex JohnFour55male
8AsruidFive85male

Getting all names starting with a or b

SELECT * FROM STUDENT WHERE name REGEXP  '^[ab]'
idnameclassmarksex
3ArnoldThree55male
6Alex JohnFour55male
8AsruidFive85male
10Big JohnFour55female
14BigySeven88female
21Babby JohnFour69female
27Big NoseThree81female
32Binn RottSeven90female

Getting all the records ending with a

SELECT * FROM STUDENT WHERE name REGEXP  'a$'
idnameclassmarksex
26CreleaSeven79male

Records with three characters

SELECT * FROM student WHERE name REGEXP '^...$'
idnameclassmarksex
13KtySeven88female

Records with four characters by using {n}

SELECT * FROM student WHERE name REGEXP '^.{4}$'
idnameclassmarksex
14BigySeven88female

Using NOT with REGEXP

SELECT * FROM STUDENT WHERE name NOT REGEXP '^[abgtrjkm]'
idnameclassmarksex
18HonnyFive75male
23HerodEight79male
26CreleaSeven79male

Case sensitive with BINARY

SELECT * FROM STUDENT WHERE name REGEXP BINARY '^[Ab]'
idnameclassmarksex
3ArnoldThree55male
6Alex JohnFour55male
8AsruidFive85male
Download sql dump of this student table
Visitors Rating
Your Rating




Google+

Post Comment This is for short comments only. Use the forum for more discussions.








HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us
©2000-2018 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer