How to Use Regex in SQL?
FREE
Regex or regular expression is a sequence of symbols and characters expressing a string or pattern to be searched for within a longer piece of text.
A regular expression or regex in SQL must be enclosed within single quotes inside the query so that the SQL function interprets the entire expression.
What is Regex?
Regex or Regular Expressions is a sequence of characters used to search and locate specific sequences of characters that match a pattern.
Example :
To return all queries where users have their email id in gmail.
REGEXP operator :
The REGEXP operator is used as a keyword before the RegEx pattern and helps match patterns.
Syntax:
SELECT statements... WHERE field_name REGEXP 'pattern';
- SELECT – Select is the SQL keyword to retrieve data from the table
- statements – Specifies the rows to be retrieved
- WHERE – This clause is used to specify a condition to fetch data
- field_name – It represents the name of a column on which the regular expression needs to be applied on.
- REGEXP – The standard keyword is written before the user-defined RegEx pattern. It is the operator used when performing regular expression pattern matches.
- my_pattern – It is the user-defined Regex pattern used to search for a subset of data from the table that matches the desired field_name.
Features of RegEx:
- RegEx in SQL is a generalized expression that is used to match patterns with various sequences of characters
- These expressions are not case-sensitive.
- It can be used to search for smaller subsets of data in database systems.
Metacharacters Supported in Regular Expressions
List of metacharacters or quantifiers used for creating complex regular expressions and defining the pattern.
| METACHARACTER | DESCRIPTION |
|---|---|
| ^ | Matches the beginning of the string. |
| \ | Alternation (either of two alternatives). |
| () | Group items in a single logical item. |
| - | Any single character (using LIKE and SIMILAR TO). |
| % | Any string (using LIKE and SIMILAR TO) |
| . | Matches any character. |
| * | Matches zero or more occurrences of the preceding subexpression. |
| + | Repetition of the previous item one or more times. |
| $ | It tells the computer that the match must be present at the end of the string or before \n at the end of the line or string. |
| ? | Match zero or one instance of the preceding string. |
| {m} | Matches exactly m occurrences of the preceding subexpression. |
| {m,} | Matches at least m occurrences of the preceding subexpression. |
| {m,n} | Matches at least m and at most n occurrences of the preceding subexpression. |
| [ … ] | Matches any character present in the square brackets. |
| [ ^ … ] | Matches any character that is not in the square brackets. |
| [a-z] | Matches any letter present in lowercase. |
| [A-Z] | Matches any letter present in uppercase. |
| [0-9] | Matches any number from 0-9. |
| [[:class:]] | Matches the character class , for example , matches [[:digit:]] to digits, [[:space:]] to space, [[:alnum:]_] to alphanumerics, etc. |
Examples for Regex in SQL
Table :
Example 1:
To get queries where the book name has the expression 'Women'.
Output:
Example 2:
To get queries where the price ends with 99.
Output:
Example 3:
To get queries where the price of the book has 4 digits.
Output:
Example 4:
To get queries where the name of the book has either 'Bird' or 'Ends'.
Output:
Example 5:
To get queries where case-sensitive 'women' is present. To check for case-sensitive patterns BINARY keyword is added.
Output:
Learn More Advanced Concepts of SQL
To get in-depth knowledge on other SQL topics head over to SQL.
Conclusion
- Regular expressions use patterns to match strings.
- Regex in SQL provides a way to query databases to find a smaller subset of data.
- The REGEXP operator is used as a keyword before the RegEx pattern to match patterns.
- It supports metacharacters that allow flexibility and control while performing pattern matching.
- The regular expressions or Regex in SQL are not case-sensitive and can be written as a combination of various data types like integers, special characters, images, and strings.