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 occurrences of the preceding subexpression. |
| {m,} | Matches at least occurrences of the preceding subexpression. |
| {m,n} | Matches at least , but not more than , 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:
SELECTcolumn_listFROMtable_nameWHEREstring_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
REGEXPoperator to compare strings in case-sensitive fashion, you can use theBINARYoperator 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.SELECTemp_nameFROMemployeesWHEREemp_name REGEXP '^j';-- matches only uppercase "J" at the beginning of the product name.SELECTemp_nameFROMemployeesWHEREemp_name REGEXP BINARY '^J';-- To find the product whose name ends with f, use 'f$' to-- match the end of a string.SELECTemp_nameFROMemployeesWHEREemp_name REGEXP 'e$';-- To find the product whose name contains the letter "k",-- use the following query:SELECTemp_nameFROMemployeesWHEREemp_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:SELECTemp_nameFROMemployeesWHEREemp_name REGEXP '^.{10}$';
Free Resources
