MySQL Regular Expression and Like
From w3cyberlearnings
Regular Expression Matacharacters
^ match start or beginning
$ match end
| match multiple options i.e match this or that
* Match zero or more instances of the string
+ Match one or more instances of the string
? Match zero or one instances of the string
. Match any single character, except a newline
{n} match string occur n times
{n,m} match string occurs n times but not more than m times
{n,} match string occurs at least n times
(a|b|c) match string with a, b, or c
(a|b|c)app match aapp, bapp, or capp
[axy] match any of a,x, or y (match of the enclosed characters)
[^axy] match any character that is not a,x, or y
(match not enclosed character)
[A-Z] match all uppercase characters
[a-z] match all lowercase characters
[0-9] match any digit
[[:<:]] start of word
[[:>:]] end of word
[[:alnum:]] equal to match[a-z],[A-Z],
and [0-9] Alphanumeric and any characters or letter
[[:alpha:]] match any letter [a-z][A-Z]
[[:blank:]] match space or tab
[[:cntrl:]] ASCII Control Character
[[:digit:]] match any numeric, equal to [0-9]
[[:graph:]] match any character except space
[[:lower:]] match lower case [a-z]
[[:upper:]] match upper case [A-Z]
[[:print:]] match printable character
[[:punct:]] match any special character, the characters that are neither
control characters nor alphanumeric
[[:space:]] match white space character (tab, new line, form feed, space etc...)
[[:xdigit:]] match any hexadecimal digit. Equal to [A-F],[a-f] and [0-9]
Create Table
CREATE TABLE cookbook ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(20) NOT NULL, year INT NOT NULL, city VARCHAR(20) NOT NULL, code VARCHAR(20) NOT NULL, PRIMARY KEY(id) );
Insert Records
INSERT INTO cookbook(name,year,city,code)
VALUES('Jimmy ol mala',1980,'Greenboro,NC','mx-be32nc312[37744]'),
('Chimmy freshfood kae',2000,'Grambling,LA','mx-bx32la3000[71245]'),
('Khom soup kea',2000,'Ruston,LA','mx-bx32la3001[71245]'),
('Mark soup suzi',2010,'Ruston,LA','mx-cb32la3001[71245]'),
('David soup majo',2009,'Ruston,LA','mx-ex32la3001[71245]'),
('Joseph hot sui',2000,'New Orlean,LA','mx-ez32la3011[71245]'),
('Tommy hot jimmy',2010,'New Orlean,LA','mx-bc32la3011[71245]'),
('Seng jim cho',2000,'Galend,Tx','mx-zz32tx3301[77340]'),
('Chorn fish chop',1999,'Galend,Tx','mx-nb32tx3301[77340]'),
('Miny seafood chop',2000,'Galend,Tx','mx-nm32tx3301[77340]'),
('soKat hotdog chop',1998,'Houston,Tx','mx-em32tx3801[77341]'),
('sokha mee coco',2001,'Houston,Tx','mx-cm32tx3801[77341]'),
('sophea da coco',2002,'Huntsville,Tx','mx-lx32tx3901[77342]'),
('sophal yi zuma',2001,'Houston,Tx','mx-px32tx3801[77341]');
Select records
mysql> SELECT * FROM cookbook; +----+----------------------+------+---------------+----------------------+ | id | name | year | city | code | +----+----------------------+------+---------------+----------------------+ | 1 | Jimmy ol mala | 1980 | Greenboro,NC | mx-be32nc312[37744] | | 2 | Chimmy freshfood kae | 2000 | Grambling,LA | mx-bx32la3000[71245] | | 3 | Khom soup kea | 2000 | Ruston,LA | mx-bx32la3001[71245] | | 4 | Mark soup suzi | 2010 | Ruston,LA | mx-cb32la3001[71245] | | 5 | David soup majo | 2009 | Ruston,LA | mx-ex32la3001[71245] | | 6 | Joseph hot sui | 2000 | New Orlean,LA | mx-ez32la3011[71245] | | 7 | Tommy hot jimmy | 2010 | New Orlean,LA | mx-bc32la3011[71245] | | 8 | Seng jim cho | 2000 | Galend,Tx | mx-zz32tx3301[77340] | | 9 | Chorn fish chop | 1999 | Galend,Tx | mx-nb32tx3301[77340] | | 10 | Miny seafood chop | 2000 | Galend,Tx | mx-nm32tx3301[77340] | | 11 | soKat hotdog chop | 1998 | Houston,Tx | mx-em32tx3801[77341] | | 12 | sokha mee coco | 2001 | Houston,Tx | mx-cm32tx3801[77341] | | 13 | sophea da coco | 2002 | Huntsville,Tx | mx-lx32tx3901[77342] | | 14 | sophal yi zuma | 2001 | Houston,Tx | mx-px32tx3801[77341] | +----+----------------------+------+---------------+----------------------+ 14 rows in set (0.00 sec)
regular expression to match any name start with s
mysql> SELECT * FROM cookbook WHERE name REGEXP '^s'; +----+-------------------+------+---------------+----------------------+ | id | name | year | city | code | +----+-------------------+------+---------------+----------------------+ | 8 | Seng jim cho | 2000 | Galend,Tx | mx-zz32tx3301[77340] | | 11 | soKat hotdog chop | 1998 | Houston,Tx | mx-em32tx3801[77341] | | 12 | sokha mee coco | 2001 | Houston,Tx | mx-cm32tx3801[77341] | | 13 | sophea da coco | 2002 | Huntsville,Tx | mx-lx32tx3901[77342] | | 14 | sophal yi zuma | 2001 | Houston,Tx | mx-px32tx3801[77341] | +----+-------------------+------+---------------+----------------------+ 5 rows in set (0.00 sec)
regular expression to match any name end with y
mysql> SELECT * FROM cookbook WHERE name REGEXP 'y$'; +----+-----------------+------+---------------+----------------------+ | id | name | year | city | code | +----+-----------------+------+---------------+----------------------+ | 7 | Tommy hot jimmy | 2010 | New Orlean,LA | mx-bc32la3011[71245] | +----+-----------------+------+---------------+----------------------+ 1 row in set (0.00 sec)
regular expression to match for all the state in texas
mysql> SELECT * FROM cookbook WHERE city REGEXP 'Tx$'; +----+-------------------+------+---------------+----------------------+ | id | name | year | city | code | +----+-------------------+------+---------------+----------------------+ | 8 | Seng jim cho | 2000 | Galend,Tx | mx-zz32tx3301[77340] | | 9 | Chorn fish chop | 1999 | Galend,Tx | mx-nb32tx3301[77340] | | 10 | Miny seafood chop | 2000 | Galend,Tx | mx-nm32tx3301[77340] | | 11 | soKat hotdog chop | 1998 | Houston,Tx | mx-em32tx3801[77341] | | 12 | sokha mee coco | 2001 | Houston,Tx | mx-cm32tx3801[77341] | | 13 | sophea da coco | 2002 | Huntsville,Tx | mx-lx32tx3901[77342] | | 14 | sophal yi zuma | 2001 | Houston,Tx | mx-px32tx3801[77341] | +----+-------------------+------+---------------+----------------------+ 7 rows in set (0.00 sec)
regular expression to match single character at the end
- one dot for one character, thus two dots for two characters
mysql> SELECT * FROM cookbook WHERE name REGEXP 'm.$'; +----+-----------------+------+---------------+----------------------+ | id | name | year | city | code | +----+-----------------+------+---------------+----------------------+ | 7 | Tommy hot jimmy | 2010 | New Orlean,LA | mx-bc32la3011[71245] | | 14 | sophal yi zuma | 2001 | Houston,Tx | mx-px32tx3801[77341] | +----+-----------------+------+---------------+----------------------+ 2 rows in set (0.01 sec)
regular exprssion to match single character at the start
mysql> SELECT * FROM cookbook WHERE name REGEXP '^.h'; +----+----------------------+------+--------------+----------------------+ | id | name | year | city | code | +----+----------------------+------+--------------+----------------------+ | 2 | Chimmy freshfood kae | 2000 | Grambling,LA | mx-bx32la3000[71245] | | 3 | Khom soup kea | 2000 | Ruston,LA | mx-bx32la3001[71245] | | 9 | Chorn fish chop | 1999 | Galend,Tx | mx-nb32tx3301[77340] | +----+----------------------+------+--------------+----------------------+ 3 rows in set (0.00 sec)
regular expression to match with alternative option
mysql> SELECT * FROM cookbook WHERE name REGEXP '^(sophal|sophea)'; +----+----------------+------+---------------+----------------------+ | id | name | year | city | code | +----+----------------+------+---------------+----------------------+ | 13 | sophea da coco | 2002 | Huntsville,Tx | mx-lx32tx3901[77342] | | 14 | sophal yi zuma | 2001 | Houston,Tx | mx-px32tx3801[77341] | +----+----------------+------+---------------+----------------------+ 2 rows in set (0.00 sec)
regular expression to match at the start with alternative option match
mysql> SELECT * FROM cookbook WHERE name REGEXP '^so(kha|phea|phal|kat)'; +----+-------------------+------+---------------+----------------------+ | id | name | year | city | code | +----+-------------------+------+---------------+----------------------+ | 11 | soKat hotdog chop | 1998 | Houston,Tx | mx-em32tx3801[77341] | | 12 | sokha mee coco | 2001 | Houston,Tx | mx-cm32tx3801[77341] | | 13 | sophea da coco | 2002 | Huntsville,Tx | mx-lx32tx3901[77342] | | 14 | sophal yi zuma | 2001 | Houston,Tx | mx-px32tx3801[77341] | +----+-------------------+------+---------------+----------------------+ 4 rows in set (0.00 sec)
regular expression to group match character
mysql> SELECT * FROM cookbook WHERE name REGEXP '^so[a-m]'; +----+-------------------+------+------------+----------------------+ | id | name | year | city | code | +----+-------------------+------+------------+----------------------+ | 11 | soKat hotdog chop | 1998 | Houston,Tx | mx-em32tx3801[77341] | | 12 | sokha mee coco | 2001 | Houston,Tx | mx-cm32tx3801[77341] | +----+-------------------+------+------------+----------------------+ 2 rows in set (0.00 sec)
regular expression to match mm
mysql> SELECT * FROM cookbook WHERE name REGEXP 'm{2}';
+----+----------------------+------+---------------+----------------------+
| id | name | year | city | code |
+----+----------------------+------+---------------+----------------------+
| 1 | Jimmy ol mala | 1980 | Greenboro,NC | mx-be32nc312[37744] |
| 2 | Chimmy freshfood kae | 2000 | Grambling,LA | mx-bx32la3000[71245] |
| 7 | Tommy hot jimmy | 2010 | New Orlean,LA | mx-bc32la3011[71245] |
+----+----------------------+------+---------------+----------------------+
3 rows in set (0.00 sec)
regular expression to match o{2} and o{1,2}
mysql> SELECT * FROM cookbook WHERE name REGEXP 'o{2}';
+----+----------------------+------+--------------+----------------------+
| id | name | year | city | code |
+----+----------------------+------+--------------+----------------------+
| 2 | Chimmy freshfood kae | 2000 | Grambling,LA | mx-bx32la3000[71245] |
| 10 | Miny seafood chop | 2000 | Galend,Tx | mx-nm32tx3301[77340] |
+----+----------------------+------+--------------+----------------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM cookbook WHERE name REGEXP 'o{1,2}';
+----+----------------------+------+---------------+----------------------+
| id | name | year | city | code |
+----+----------------------+------+---------------+----------------------+
| 1 | Jimmy ol mala | 1980 | Greenboro,NC | mx-be32nc312[37744] |
| 2 | Chimmy freshfood kae | 2000 | Grambling,LA | mx-bx32la3000[71245] |
| 3 | Khom soup kea | 2000 | Ruston,LA | mx-bx32la3001[71245] |
| 4 | Mark soup suzi | 2010 | Ruston,LA | mx-cb32la3001[71245] |
| 5 | David soup majo | 2009 | Ruston,LA | mx-ex32la3001[71245] |
| 6 | Joseph hot sui | 2000 | New Orlean,LA | mx-ez32la3011[71245] |
| 7 | Tommy hot jimmy | 2010 | New Orlean,LA | mx-bc32la3011[71245] |
| 8 | Seng jim cho | 2000 | Galend,Tx | mx-zz32tx3301[77340] |
| 9 | Chorn fish chop | 1999 | Galend,Tx | mx-nb32tx3301[77340] |
| 10 | Miny seafood chop | 2000 | Galend,Tx | mx-nm32tx3301[77340] |
| 11 | soKat hotdog chop | 1998 | Houston,Tx | mx-em32tx3801[77341] |
| 12 | sokha mee coco | 2001 | Houston,Tx | mx-cm32tx3801[77341] |
| 13 | sophea da coco | 2002 | Huntsville,Tx | mx-lx32tx3901[77342] |
| 14 | sophal yi zuma | 2001 | Houston,Tx | mx-px32tx3801[77341] |
+----+----------------------+------+---------------+----------------------+
14 rows in set (0.00 sec)
regular expression to match between word
mysql> SELECT * FROM cookbook WHERE name REGEXP '[[:<:]]soup[[:>:]]'; +----+-----------------+------+-----------+----------------------+ | id | name | year | city | code | +----+-----------------+------+-----------+----------------------+ | 3 | Khom soup kea | 2000 | Ruston,LA | mx-bx32la3001[71245] | | 4 | Mark soup suzi | 2010 | Ruston,LA | mx-cb32la3001[71245] | | 5 | David soup majo | 2009 | Ruston,LA | mx-ex32la3001[71245] | +----+-----------------+------+-----------+----------------------+ 3 rows in set (0.01 sec)
regular expression escape special character
mysql> SELECT * FROM cookbook WHERE code REGEXP '\\[71245\\]$'; +----+----------------------+------+---------------+----------------------+ | id | name | year | city | code | +----+----------------------+------+---------------+----------------------+ | 2 | Chimmy freshfood kae | 2000 | Grambling,LA | mx-bx32la3000[71245] | | 3 | Khom soup kea | 2000 | Ruston,LA | mx-bx32la3001[71245] | | 4 | Mark soup suzi | 2010 | Ruston,LA | mx-cb32la3001[71245] | | 5 | David soup majo | 2009 | Ruston,LA | mx-ex32la3001[71245] | | 6 | Joseph hot sui | 2000 | New Orlean,LA | mx-ez32la3011[71245] | | 7 | Tommy hot jimmy | 2010 | New Orlean,LA | mx-bc32la3011[71245] | +----+----------------------+------+---------------+----------------------+ 6 rows in set (0.00 sec)
regular expression to match bx or ex
mysql> SELECT * FROM cookbook WHERE code REGEXP 'mx\\-(bx|ex)'; +----+----------------------+------+--------------+----------------------+ | id | name | year | city | code | +----+----------------------+------+--------------+----------------------+ | 2 | Chimmy freshfood kae | 2000 | Grambling,LA | mx-bx32la3000[71245] | | 3 | Khom soup kea | 2000 | Ruston,LA | mx-bx32la3001[71245] | | 5 | David soup majo | 2009 | Ruston,LA | mx-ex32la3001[71245] | +----+----------------------+------+--------------+----------------------+ 3 rows in set (0.00 sec)
another similar query
mysql> SELECT * FROM cookbook WHERE code REGEXP 'mx[[:punct:]](bx|ex)'; +----+----------------------+------+--------------+----------------------+ | id | name | year | city | code | +----+----------------------+------+--------------+----------------------+ | 2 | Chimmy freshfood kae | 2000 | Grambling,LA | mx-bx32la3000[71245] | | 3 | Khom soup kea | 2000 | Ruston,LA | mx-bx32la3001[71245] | | 5 | David soup majo | 2009 | Ruston,LA | mx-ex32la3001[71245] | +----+----------------------+------+--------------+----------------------+ 3 rows in set (0.00 sec)
