Don't let the Lockdown slow you Down - Enroll Now and Get 3 Course at 24,999/- Only. Explore Now!

General

SQL REGEX

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.

PatternDescription 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|p3Match 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.

PatternDescription 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|p3Match 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.

Scroll Up
Besant Technologies WhatsApp