ITSourceCode.com

We Exist to Provide 100% Free Source Code and Tutorials

Custom Search

SQL Regular Expressions

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

Check Your Domain Ranking

Leave a Reply

Your email address will not be published. Required fields are marked *

ITSourceCode.com © 2016 Frontier Theme

Subscribe For Latest Updates

Signup for our newsletter and get notified when we publish new articles for free!