Related Tags

regex
sql

# How to create regular expressions in SQL

A regular expression is a sequence of characters used to match a pattern to a string. You can use it for searching text and validating input.

Remember that a regular expression is not​ the property of a particular language.

A regular expression must be enclosed within single quotes. Doing so ensures that the entire expression is interpreted by the SQL function.

## Metacharacters supported in regular expressions

Character Description
. Matches any character.
+ Matches one or more occurrences of the preceding subexpression.
? Matches zero or one occurrence of the preceding subexpression.
$*$ Matches zero or more occurrences of the preceding subexpression.
{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$, but not more than $n$, occurrences of the preceding subexpression.
[ … ] Matches any character in the list.
[ ^ … ] Matches any character not in the list.
\ Treats the subsequent metacharacter in the expression as a literal.
^ Match the subsequent expression only when it occurs at the beginning of a line.
$Match the preceding expression only when it occurs at the end of a line. ## Creating regular expressions in SQL MySQL allows you to match patterns by using the REGEXP operator. See the syntax below: SELECT column_list FROM table_name WHERE string_column REGEXP pattern; ## Examples Consider the following employees table: CREATE TABLE employees( emp_id int, emp_name varchar(20), dept varchar(20), age int ); INSERT INTO employees(emp_id,emp_name,dept,age) VALUES(1, "John", "Intern",25); INSERT INTO employees(emp_id,emp_name,dept,age) VALUES(5, "jake", "Intern",25); INSERT INTO employees(emp_id,emp_name,dept,age) VALUES(2, "David", "Intern",30); INSERT INTO employees(emp_id,emp_name,dept,age) VALUES(3, "Mike", "Engineer",29); INSERT INTO employees(emp_id,emp_name,dept,age) VALUES(4, "Alex", "HR",27); id name dept age 1 John Intern 25 2 David Intern 30 3 Mike Engineer 29 4 Alex HR 27 5 jake Intern 25 Take a look at the examples below; they show how to create regular expressions (and use them) with the REGEXP operator. If you want the REGEXP operator to compare strings in case-sensitive fashion, you can use the BINARY operator to cast a string to a binary string -- To find employees whose names start with the character j, use -- the metacharacter '^' to match at the beginning of the name. SELECT emp_name FROM employees WHERE emp_name REGEXP '^j'; -- matches only uppercase "J" at the beginning of the product name. SELECT emp_name FROM employees WHERE emp_name REGEXP BINARY '^J'; -- To find the product whose name ends with f, use 'f$' to
-- match the end of a string.
SELECT
emp_name
FROM
employees
WHERE
emp_name REGEXP 'e$'; -- To find the product whose name contains the letter "k", -- use the following query: SELECT emp_name FROM employees WHERE emp_name REGEXP 'k'; -- To find the product whose name contains exactly 10 characters, -- use ‘^' and ‘$ to match the beginning and end of the product name,
-- and repeat {10} times of any character ‘.' in between as shown
-- in the following query:
SELECT
emp_name
FROM
employees
WHERE
emp_name REGEXP '^.{10}\$';



RELATED TAGS

regex
sql