SQL Regular Expressions

0
30

sqltuts

Following is the table of pattern, which can be used along with REGEXP operator in a query for a selected table in the database.

Pattern What the pattern is used /described for
^ Beginning of a given string
$ End of a given string
. Any single character you wish to take
[…] Any character   that has been listed between the square brackets
[^…] Any character that has not been listed between the square brackets
p1|p2|p3 Alternation; matches any of the patterns p1, p2, or p3
* Zero or more instances of preceding element you desired for
+ One or more instances of preceding element you desired for
{n} n instances of preceding element you desired for
{m,n} m through n instances of preceding element you desired for

Examples:

Query to find all the names starting with ‘ls’

  1. mysql> SELECT name FROM person_Table WHERE name REGEXP '^ls';

Query to find all the names ending with ‘sh’

  1. mysql> SELECT name FROM person_table WHERE name REGEXP 'sh$';

Query to find all the names, which contain ‘lok’

  1. mysql> SELECT name FROM person_table WHERE name REGEXP 'lok';

Query to find all the names starting with a vowel and ending with ‘rm’

  1. mysql> SELECT name FROM person_table WHERE name REGEXP '^[aeiou]|rm$';
  1. mysql> SELECT 'Monty!' REGEXP '.*';
  2.         -> 1
  3. mysql> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';
  4.         -> 1
  5. mysql> SELECT 'a' REGEXP 'A', 'a' REGEXP BINARY 'A';
  6.         -> 1  0
  7. mysql> SELECT 'a' REGEXP '^[a-d]';
  8.         -> 1

 

[a-dX], [^a-dX]
nbsp;

Matches any character that is (or is not, if ^ is used) either a, b, c, d or X. A – Character between two other characters forms a range that matches all characters from the first character to the second. For example, [0-9] matches any decimal digit. To include a literal] character, it must immediately follow the opening bracket [. To include a literal – character, it must be written first or last. Any character that does not have a defined special meaning inside a [] pair matches only itself.

 

 

  1. mysql> SELECT 'aXbc' REGEXP '[a-dXYZ]';                 -> 1
  2. mysql> SELECT 'aXbc' REGEXP '^[a-dXYZ]$';               -> 0
  3. mysql> SELECT 'aXbc' REGEXP '^[a-dXYZ]+$';              -> 1
  4. mysql> SELECT 'aXbc' REGEXP '^[^a-dXYZ]+$';             -> 0
  5. mysql> SELECT 'gheis' REGEXP '^[^a-dXYZ]+$';            -> 1
  6. mysql> SELECT 'gheisa' REGEXP '^[^a-dXYZ]+$';           -> 0

 

 

Facebook Comments
SHARE
Previous articleSQL NULL Values
Next articleSQL Primary Key Constraint

Hello Itsourcecoders, welcome to itsourcecode.com. I’m Joken Villanueva, MIT a passionate Blogger, Programmer and a Hobbyist. I started Itsourcecode because I wanted to give back and Share all the learnings and knowledge I’ve learned in my career and I believe through this website I would be able to help and assist those newbie programmers in enhancing their skills from different programming languages. So let us all help each other by sharing our ideas!

LEAVE A REPLY

Please enter your comment!
Please enter your name here