Image from Shutterstock

REGULAR EXPRESSION IN MYSQL

Improve your Pattern Matching with these Queries

REGULAR EXPRESSION:

Regular Expressions are sequence/pattern of characters that is used to define a search pattern mainly used in string searching algorithms and pattern matching in programming languages like C++ and python.

Pattern matching is also extensively used in SQL queries to find the data that’s present in certain pattern.

This article attempts to explain all the regular expression in MySQL and execute the same in Employees Database and Olympics Database.

Snippet from the Olympics Database

Snippet from the Employee Database

^ :

Matches the beginning of the string.

Query to obtain all the records that has last name starting with Al

Answer for the above query

and the important thing to note is regexp in MySQL is not case sensitive so the query below gives same output as the above one.

Here instead of Al, al is used but since regexp in MySQL is not case sensitive this query gives the same output as the last one

Answer for the above query

$ :

Matches the end of string.

query to return records with last_name ending with ar

answer for the above query

. or .{} :

Dot character matches any single character except for a new line and can be used to define the number of characters you are expecting a string to have like,

query to obtain last_name having 5 characters

This query uses ^ and $ match the beginning and ending of last_name and five ‘.’ to define 5 characters

answer for the above query

As you can see the query returns records that has only 5 characters in last_name.

This query can also be written like

query to obtain last_name having 5 characters

.{n} can be used instead of repeating (.) n times

answer for the above query

[ ] :

using square brackets to optimize the pattern matching even more.

Records containing specific characters in first_name:

query to return first_name containing x,y,z .

answer for the above query

Records that has first_name starting with specific characters:

query to return first_name containing starting with x,y,z

answer for the above query

Records that has first_name ending with specific characters:

query to return first_name containing ending with x,y,z

answer for the above query

CONCLUSION:

This article contains some of the basic regexp patterns, hope you find this article helpful.

Thank you……….