What is Pattern matching in SQL and how it is done?

In SQL, we sometimes need to filter our resultset based on some pattern matching techniques. SQL has a standard pattern matching technique using the 'LIKE' operator . But, it also supports the regular expression pattern matching for better functionality.

Generally, the REGEXP_LIKE(column_name, 'regex') function is used for pattern matching in SQL. SQL also supports some operators that work similar to this function, these are: 'REGEXP' and 'RLIKE' operator . So in this blog, we will learn about all those functions and operators that provide pattern matching functionality in SQL.

Note: In this blog, we will take all the examples using the MySQL database only. We'll be using the below-mentioned course table for demonstrating the examples. The table is as follows:

Now let us learn about these pattern-matching functions and operators one-by-one.

LIKE Operator

The LIKE operator provides standard pattern matching in SQL that is always used after a WHERE clause. It matches any pattern based on some conditions provided using the wildcard characters.

Some of the commonly used wildcard characters in MySQL are as follows:

'%' represents zero or more characters.
'_' represents exactly 1 character.

In MySQL the syntax of the LIKE operator can be as follows:

SELECT [table.columns..] FROM table WHERE table.column LIKE 'wildcard_characters';

Now let us take an example using the wildcard characters ' d% ' that searches for ' d ' in the beginning and then there can be zero or any number of characters because % denotes zero or more characters.

The MySQL query for the above operation can be:

SELECT * FROM course WHERE course_name LIKE 'd%';

The output for the above query can be as follows:

It is to be noted that pattern matching in MySQL is case insensitive. In the above example, you can see that the patterns starting with 'D' are also present in the output.

Note: To make the pattern matching case sensitive in MySQL, the ' BINARY ' operator is used.

The query for the above pattern matching using the BINARY operator along with the LIKE operator can be as follows:

SELECT * FROM course WHERE course_name LIKE BINARY 'd%';

The output for the above query can be as follows:

In the above output, you can see that the pattern matching is performed in a case sensitive manner. Thus, we do not get any resultset as there are no strings starting with the d character.

Now, if we use the wildcard characters %s , it will search for all such patterns that can have 0 or any number of characters in the beginning but has a ' s ' character in the end.

The MySQL query for pattern matching using the above wildcard characters and LIKE operator can be as follows:

SELECT * FROM course WHERE course_name LIKE '%s';

The output for the above query can be as follows:

Now, if we use the wildcard characters ____ (4 underscores), it will search for exactly 4 characters.

The MySQL query for pattern matching using the above wildcard characters and LIKE operator can be as follows:

SELECT * FROM course WHERE course_name LIKE '____';

The output for the above query can be as follows:

Now, if we use the wildcard characters D%s , it will search for all such patterns that have a D character, in the beginning, a s character at the end, and 0 or any number of characters in between them.

The MySQL query for pattern matching using the above wildcard characters and LIKE operator can be as follows:

SELECT * FROM course WHERE course_name LIKE 'D%s';

The output for the above query can be as follows:

Note: For using negation in pattern matching, the NOT operator is used in MySQL.

The syntax for using the NOT operator along with the LIKE operator can be as follows:

SELECT [table.columns..] FROM table WHERE table.column NOT LIKE 'wildcard_characters';

The MySQL query for pattern matching using the above wildcard characters, the LIKE and NOT operators can be as follows:

SELECT * FROM course WHERE course_name NOT LIKE 'D%s';

The output for the above query can be as follows:

REGEXP_LIKE(column_name, 'regex') Function

MySQL also has an extended regular expression pattern matching using the REGEXP_LIKE(column_name, 'regex') function, which takes the column name and the regular expression as parameters.

Some of the commonly used characters in MySQL for regular expression are as follows:

'^' represents the starting of the regular expression
'$' represents the end of the regular expression
'.' represents a single character

The syntax for the REGEXP_LIKE(column_name, 'regex') function can be as follows:

SELECT [table.columns..] FROM table WHERE REGEXP_LIKE (table.column, 'regex');

Note: In all the below examples, we'll use the regex - [A-Za-z]{4,} that accepts all the characters from A to Z in both the cases i.e. upper and lower. This regular expression will accept at least 4 characters and will not allow blank spaces in the pattern.

The MySQL query for pattern matching using the above regular expression in the function can be as follows:

SELECT * FROM course WHERE REGEXP_LIKE (course_name, '^[A-Za-z]{4,}$');

The output for the above query can be as follows:

Here also, the NOT operator is used for negation. The MySQL query for pattern matching using the above regular expression and NOT operator in the function can be as follows:

SELECT * FROM course WHERE NOT REGEXP_LIKE (course_name, '^[A-Za-z]{4,}$');

The output for the above query can be as follows:

REGEXP Operator

The REGEXP operator performs exactly the same functionality as the REGEXP_LIKE(column_name, 'regex') function.

The syntax for the REGEXP operator can be as follows:

SELECT [table.columns..] FROM table WHERE table.column REGEXP 'regex';

The MySQL query for pattern matching using the same regular expression as above in the REGEXP operator can be as follows:

SELECT * FROM course WHERE course_name REGEXP '^[A-Za-z]{4,}$';

The output for the above query can be as follows:

The NOT operator can be used along with the REGEXP operator in order to perform negation. The MySQL query for pattern matching using the same regular expression, REGEXP and NOT operator can be as follows:

SELECT * FROM course WHERE course_name NOT REGEXP '^[A-Za-z]{4,}$';

The output for the above query can be as follows:

RLIKE Operator

The RLIKE operator also performs the same functionality as the above two.

The syntax for the RLIKE operator can be as follows:

SELECT [table.columns..] FROM table WHERE table.column RLIKE 'regex';

The MySQL query for pattern matching using the same regular expression as above in the RLIKE operator can be as follows:

SELECT * FROM course WHERE course_name RLIKE '^[A-Za-z]{4,}$';

The output for the above query can be as follows:

Here also, the NOT operator can be used along with the RLIKE operator in order to perform negation. The MySQL query for pattern matching using the same regular expression, RLIKE and NOT operator can be as follows:

SELECT * FROM course WHERE course_name NOT RLIKE '^[A-Za-z]{4,}$';

The output for the above query can be as follows:

This is all about the various pattern-matching techniques in SQL. Hope you learned something new today. That's it for this blog.

Do share this blog with your friends to spread the knowledge. Visit our YouTube channel for more content. You can read more blogs from here .

Keep Learning :)

Team AfterAcademy!

AfterAcademy