AWS Global Infrastructure

Databases

Topics Covered
  • Cassandra (14 Blogs)
  • MongoDB Dev and Admin (15 Blogs)
  • MySQL (55 Blogs)
  • SQL Essentials Training and Certification (9 Blogs)
SEE MORE

What is SQL Regex and how to implement it?

Last updated on Mar 28,2022 97.7K Views

Swatee Chand
Sr Research Analyst at Edureka. A techno freak who likes to explore... Sr Research Analyst at Edureka. A techno freak who likes to explore different technologies. Likes to follow the technology trends in market and write...
14 / 37 Blog from Introduction to SQL

Databases are huge dumps of data where the data is stored in an organized manner. But many a time we come across situations where we need to retrieve some data but don’t have sufficient information to filter it out. For such cases, SQL provides an amazing feature called Regular Expressions. Through the medium of this article, I will give you complete insights into what actually are SQL RegEx and how to implement them.

Following are the topics I will be discussing in this article:

What is RegEx?

A Regular Expression is popularly known as RegEx, is a generalized expression that is used to match patterns with various sequences of characters. A RegEx can be a combination of different data types such as integer, special characters, Strings, images, etc. Generally, these patterns are used in String searching algorithms in order to perform find or find and replace operations on Strings, or for validating the input.

Below I have listed down major features of SQL Regex:

  1. It provides a powerful and flexible pattern match.
  2. Helps in implementing powerful search utilities for the database systems
  3. Supports a number of metacharacters for more flexibility and control when performing pattern matching.
  4. In RegEx, the backslash character is used as an escape character.
  5. RegEx are not case sensitive.

Now that you are aware of what are Regex, let’s now see what are various RegEx supported by SQL.

SQL Regex 

Below I have listed down all the Regular Expressions that can be used in SQL.

PatternDescription
*Matches zero or more instances of the preceding String
+Matches one or more instances of the preceding String
.Matches any single character
?Matches zero or one instance of the preceding Strings
^^ matches the beginning of a String
$$ matches the ending of a String
[abc]Matches any character listed in between the square brackets
[^abc]Matches any character not listed in between the square brackets
[A-Z]Matches any letter in uppercase
[a-z]Matches any letter in lowercase
[0-9]Matches any digit between 0-9
[[:<:]]Matches the beginning of words
[[:>:]]Matches the end of words
[:class:]Matches any character class
p1|p2|p3Mathes any of the specified pattern
{n}Matches n instances of the preceding element
{m,n}Matches m through n instances of the preceding element

Let’s now dive a bit deeper and see how to form a RegEx in SQL.

Syntax for using SQL Regex

Using Regex is really simple. All you need to do is follow the below-shown syntax:

SELECT statements... WHERE field_name REGEXP 'my_pattern';

Explanation

  • SELECT  – Select is the standard SQL keyword to retrieve data from the table
  • statements – This specifies the rows to be retrieved
  • WHEREWHERE clause is used to specify a condition while fetching the data
  • field_name – It represents the name of a column on which the regular expression needs to be applied on.
  • REGEXP – It is the keyword that precedes the RegEx pattern
  • my_pattern – It is the user-defined RegEx pattern to search data

Now that you know how to form a RegEx statement, let me show how SQL RegEx are implemented.

SQL RegEx Implementations

For the practical implementation, I will be using the following table to perform RegEx queries.
table - sql regex - edureka

  • Match the specified String
SELECT * FROM `learnerdetails` WHERE `course_name` REGEXP 'SQL';
  • Match beginning of string(^23)
SELECT * FROM `learnerdetails` WHERE `course_Id` REGEXP '^23';
  • Match zero or one instance of the strings preceding it(Ja?)
SELECT * FROM learnerdetails WHERE course_name REGEXP 'Ja?';
  • Matches any of the patterns ‘w|ja’
SELECT learner_name FROM learnerdetails WHERE course_name REGEXP 'w|ja' ;
  • Match the end of a string (yahoo.com $)
SELECT learner_name FROM learnerdetails WHERE learner_email REGEXP 'yahoo.com$';

I hope this gives you an idea of how to form the queries. There are a lot more combinations with which you can play around with. With this, I would like to conclude this article on SQL RegEx. For more information on SQL or Databases, you can refer to our comprehensive reading list here: Databases Edureka.

If you wish to get a structured training on MySQL, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.

Got a question for us? Please mention it in the comments section of ”SQL RegEx” and I will get back to you.

Comments
0 Comments

Join the discussion

Browse Categories

webinar REGISTER FOR FREE WEBINAR
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP

Subscribe to our Newsletter, and get personalized recommendations.

image not found!
image not found!

What is SQL Regex and how to implement it?

edureka.co