REGEXP: The regular expression string match in data of mysql table

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

STRING Functions Case Condition Checking

Subhendu Mohapatra

Author

🎥 Join me live on YouTube

Passionate about coding and teaching, I publish practical tutorials on PHP, Python, JavaScript, SQL, and web development. My goal is to make learning simple, engaging, and project‑oriented with real examples and source code.