SQL REGEX?
In this tutorial, we shall learn about regular expressions in SQL. Let’s get started.
What is REGEX?
REG EX or Regular Expressions contain a sequence of characters that are used to search and find a particular sequence of characters that matches a pattern. It is a rule that defines the way characters should appear on an expression. Search pattern is determined by the sequence of characters or text. Say for example, if you want to identify the data that contains a combination namely a student name, contact number, e-mail id etc., we can use regular expressions. It is also used in cases where you need to identify a particular text pattern or you need to apply a filter over numbers, texts or special character data. It is also used for data parsing in ETL by forming rules to find patterns present in code and to create rules for both inbound and outbound traffic.
A regular expression can be written as a combination of various data types namely integers, special characters, images, strings, etc., We can use these patterns in string searching algorithms so as to perform:
- Find
- Find as well as replace on strings
- Validating the input
An SQL Server database, will not support in-built RegEx functions, whereas oracle and MYSQL support them. Still, the SQL server provides in-built functions to overcome complex problems. Some of those functions are REPLACE, SUBSTRING, CHARINDEX, PATINDEX, LIKE, etc., These functions can be combined together to create a complex query. Such queries need more effort and time to create and they are more difficult to maintain. But, if we write a query for a bigger table, these will be very effective.
Features of RegEx in SQL
- RegEx furnish more flexibility and power in pattern matching
- It supports to implement robust search utilities for DB systems
- It provides support for various meta characters to achieve more flexibility and control while performing pattern matching
- We can use the backslash character as an escape character in RegEx.
- These expressions are not case sensitive.
List of Regular Expressions used in SQL
The following table contains the list of regular expressions in SQL.
| Pattern | Description of the pattern |
|---|---|
| + | Match One or more instances of preceding string |
| * | Match zero or more instances of preceding string |
| . | Matches any one character |
| ? | Match zero or one instance of preceding string |
| ^ | Matches the beginning of a string |
| $ | Matches the end of a string |
| [0-9] | Matches any number from 0 to 9 |
| [abc] | Match any character present in the square brackets |
| [^abc] | Match any character which is not present in the square brackets |
| [a-z] | Matches any letter present in lower case |
| [A-Z] | Matches any letter present in upper case |
| [[:<:]] | Match the beginning of words |
| [[:>:]] | Match the ending of words |
| p1|p2|p3 | Match any particular pattern in the list |
| [:class:] | Match any character class |
| {n} | Match n instances of the element preceding |
| {m,n} | Match m till n instances of the element preceding |
The syntax for RegEx:
The basic syntax for using SQL RegEx is given as under:
SELECT statements..... WHERE field_name REGEXP 'my_pattern';
where
SELECT: It is a standard keyword in SQL which is used to retrieve data from the table
Statements: indicates the rows that have to be retrieved
WHERE: used to write a condition while we fetch data
field_name: column name where we are going to apply the regular expression
REGEXP: standard keyword which precedes the RegEx pattern
my_pattern: the RegEx pattern created by a user to search for data.
How will you implement SQL RegEx?
The following table helps you to understand the way to implement SQL RegEx.
| Pattern | Description of the pattern |
|---|---|
| + | Match One or more instances of preceding string |
| * | Match zero or more instances of preceding string |
| . | Matches any one character |
| ? | Match zero or one instance of preceding string |
| ^ | Matches the beginning of a string |
| $ | Matches the end of a string |
| [0-9] | Matches any number from 0 to 9 |
| [abc] | Match any character present in the square brackets |
| [^abc] | Match any character which is not present in the square brackets |
| [a-z] | Matches any letter present in lower case |
| [A-Z] | Matches any letter present in upper case |
| [[:<:]] | Match the beginning of words |
| [[:>:]] | Match the ending of words |
| p1|p2|p3 | Match any particular pattern in the list |
| [:class:] | Match any character class |
| {n} | Match n instances of the element preceding |
| {m,n} | Match m till n instances of the element preceding |
Let’s see how to implement SQL RegEx using the above table.
1. To match a specific string, use the following syntax:
SELECT * FROM `studentrecord` WHERE ` subject_name ` REGEXP 'SQL';
2. To match the beginning of the string say,^24 , we can use the following syntax:
SELECT * FROM `studentrecord` WHERE ` subject_name ` REGEXP '^24';
3. To match a Zero or one instance of the preceding string, say py?
SELECT * FROM `studentrecord` WHERE ` subject_name ` REGEXP ' py?’;
4. To match any pattern, say wlpy|
SELECT * FROM `studentrecord` WHERE ` subject_name ` REGEXP ' w|py?’;
5. To match the end of a string, say gmail.com$
SELECT * FROM `studentrecord` WHERE ` subject_name ` REGEXP ' gmail.com$’;
With this, we are concluding the tutorial for regular expressions in SQL. Hope this will be very useful.
