Mysql REGEXP
From w3cyberlearnings
MySQL REGEXP Function
This function uses for regular expression matching.
Syntax REGEXP
- expre is the string
- pat is the regular expression pattern matching
// normal regular expression matching expre REGEXP pat // negative regular expression matching expre NOT REGEXP pat // synonym for REGEXP expre RLIKE pat
Example 1: . match any single character
. is matched any character, * matches any sequence of zero or more character
mysql> SELECT 'great leader' REGEXP '.*'; +----------------------------+ | 'great leader' REGEXP '.*' | +----------------------------+ | 1 | +----------------------------+ 1 row in set (0.00 sec)
Example 2
Match word, but case in-sensitive
mysql> SELECT 'apple' REGEXP 'APPLE'; +------------------------+ | 'apple' REGEXP 'APPLE' | +------------------------+ | 1 | +------------------------+ 1 row in set (0.00 sec)
Example 3: Make case sensitive match
Match word, but case sensitive by using BINARY key word.
mysql> SELECT 'apple' REGEXP BINARY 'APPLE'; +-------------------------------+ | 'apple' REGEXP BINARY 'APPLE' | +-------------------------------+ | 0 | +-------------------------------+ 1 row in set (0.00 sec)
Example 4: match beginning and end
^ matches at the beginning, and $ matches at the end.
mysql> SELECT 'good teacher teaches' REGEXP '^good .* teaches$'; +---------------------------------------------------+ | 'good teacher teaches' REGEXP '^good .* teaches$' | +---------------------------------------------------+ | 1 | +---------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT 'good teacher teaches' REGEXP '^good .* girl$'; +------------------------------------------------+ | 'good teacher teaches' REGEXP '^good .* girl$' | +------------------------------------------------+ | 0 | +------------------------------------------------+ 1 row in set (0.00 sec)
Example 5: Match one or many
mysql> SELECT 'apppple' REGEXP 'ap*le'; +--------------------------+ | 'apppple' REGEXP 'ap*le' | +--------------------------+ | 1 | +--------------------------+ 1 row in set (0.00 sec) mysql> SELECT 'apppple' REGEXP 'aple'; +-------------------------+ | 'apppple' REGEXP 'aple' | +-------------------------+ | 0 | +-------------------------+ 1 row in set (0.00 sec)
Example 6: Match at least one
+ match any sequence at least one or more
mysql> SELECT 'Ban' REGEXP '^Ba+n'; +----------------------+ | 'Ban' REGEXP '^Ba+n' | +----------------------+ | 1 | +----------------------+ 1 row in set (0.00 sec) mysql> SELECT 'Bn' REGEXP '^Ba+n'; +---------------------+ | 'Bn' REGEXP '^Ba+n' | +---------------------+ | 0 | +---------------------+ 1 row in set (0.00 sec)
Example 7: Match zero or one
? matches at zero or one
mysql> SELECT 'Bn' REGEXP '^Ba?n'; +---------------------+ | 'Bn' REGEXP '^Ba?n' | +---------------------+ | 1 | +---------------------+ 1 row in set (0.00 sec)
Example 8: Alternative match
| is an alternative match. Match this one or another one.
mysql> SELECT 'apple' REGEXP 'fruit|apple'; +------------------------------+ | 'apple' REGEXP 'fruit|apple' | +------------------------------+ | 1 | +------------------------------+ 1 row in set (0.00 sec) mysql> SELECT 'apple' REGEXP 'fruit|banana|apple'; +-------------------------------------+ | 'apple' REGEXP 'fruit|banana|apple' | +-------------------------------------+ | 1 | +-------------------------------------+ 1 row in set (0.01 sec)
Example 9: Group match
mysql> SELECT 'ababab' REGEXP '^(ab)*$'; +---------------------------+ | 'ababab' REGEXP '^(ab)*$' | +---------------------------+ | 1 | +---------------------------+ 1 row in set (0.00 sec) mysql> SELECT 'ababa' REGEXP '^(ab)*$'; +--------------------------+ | 'ababa' REGEXP '^(ab)*$' | +--------------------------+ | 0 | +--------------------------+ 1 row in set (0.00 sec)
Example 10: Alternative and Group Match
mysql> SELECT 'fruit' REGEXP '^(meat|fruit)'; +--------------------------------+ | 'fruit' REGEXP '^(meat|fruit)' | +--------------------------------+ | 1 | +--------------------------------+ 1 row in set (0.00 sec) mysql> SELECT 'A fruit' REGEXP '(meat|fruit)$'; +----------------------------------+ | 'A fruit' REGEXP '(meat|fruit)$' | +----------------------------------+ | 1 | +----------------------------------+ 1 row in set (0.00 sec)
Example 11: Match in range
- b* can be written as b{0,}
- b+ can be written as b{1,}
- b? can be written as b{0,1}
mysql> SELECT 'aaaa' REGEXP 'a{0,4}';
+------------------------+
| 'aaaa' REGEXP 'a{0,4}' |
+------------------------+
| 1 |
+------------------------+
1 row in set (0.00 sec)
mysql> SELECT 'aaaa' REGEXP 'a{0,2}';
+------------------------+
| 'aaaa' REGEXP 'a{0,2}' |
+------------------------+
| 1 |
+------------------------+
1 row in set (0.00 sec)
Example 12: Nagative match or match in range
- nagative match for all characters: [^a-z]
- match in range: [a-z]
- match in range but case sensitive: BINARY [a-z] or BINARY [A-Z]
mysql> SELECT 'abc' REGEXP '[a-z]'; +----------------------+ | 'abc' REGEXP '[a-z]' | +----------------------+ | 1 | +----------------------+ 1 row in set (0.00 sec) mysql> SELECT 'abc' REGEXP '[A-Z]'; +----------------------+ | 'abc' REGEXP '[A-Z]' | +----------------------+ | 1 | +----------------------+ 1 row in set (0.00 sec) mysql> SELECT 'abc' REGEXP BINARY '[A-Z]'; +-----------------------------+ | 'abc' REGEXP BINARY '[A-Z]' | +-----------------------------+ | 0 | +-----------------------------+ 1 row in set (0.00 sec) mysql> SELECT 'abc' REGEXP '[^a-z]'; +-----------------------+ | 'abc' REGEXP '[^a-z]' | +-----------------------+ | 0 | +-----------------------+ 1 row in set (0.00 sec)
Example 13: bracket expression uses for matching a specific character class
[[:alnum:]] is Alphanumeric characters [[:alpha:]] is Alphabetic characters [[:blank:]] is whitespace characters [[:cntrl:]] is control characters [[:digit:]] is digit characters [[:graph:]] is graphics characters [[:lower:]] is lower case alphabetic characters [[:print:]] is graphic or space characters [[:punct:]] is punctuation characters [[:space:]] is space, tab, newline, and carriage return [[:upper:]] is uppercase alphabetic characters [[:xdigit:]] is hexadecimal digit character
mysql> SELECT 'wonderful leader' REGEXP '[[:alnum:]]+'; +------------------------------------------+ | 'wonderful leader' REGEXP '[[:alnum:]]+' | +------------------------------------------+ | 1 | +------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT '#&' REGEXP '[[:alnum:]]+'; +----------------------------+ | '#&' REGEXP '[[:alnum:]]+' | +----------------------------+ | 0 | +----------------------------+ 1 row in set (0.00 sec)
Example 14: word boundaries
[[:<:]] word [[:>:]]
mysql> SELECT 'School a' REGEXP '[[:<:]]School a[[:>:]]'; +--------------------------------------------+ | 'School a' REGEXP '[[:<:]]School a[[:>:]]' | +--------------------------------------------+ | 1 | +--------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT 'School at' REGEXP '[[:<:]]School a[[:>:]]'; +---------------------------------------------+ | 'School at' REGEXP '[[:<:]]School a[[:>:]]' | +---------------------------------------------+ | 0 | +---------------------------------------------+ 1 row in set (0.00 sec)
Example 15: Escape special character
Use two backslash characters to escape special character
mysql> SELECT 'a+2' REGEXP 'a+2'; +--------------------+ | 'a+2' REGEXP 'a+2' | +--------------------+ | 0 | +--------------------+ 1 row in set (0.00 sec) mysql> SELECT 'a+2' REGEXP 'a\\+2'; +----------------------+ | 'a+2' REGEXP 'a\\+2' | +----------------------+ | 1 | +----------------------+ 1 row in set (0.00 sec)
Example 16: Permissible character
Name Character
NUL 0
SOH 001
STX 002
ETX 003
EOT 004
ENQ 005
ACK 006
BEL 007
alert 007
BS 010
backspace '\b'
HT 011
tab '\t'
LF 012
newline '\n'
VT 013
vertical-tab '\v'
FF 014
form-feed '\f'
CR 015
carriage-return '\r'
SO 016
SI 017
DLE 020
DC1 021
DC2 022
DC3 023
DC4 024
NAK 025
SYN 026
ETB 027
CAN 030
EM 031
SUB 032
ESC 033
IS4 034
FS 034
IS3 035
GS 035
IS2 036
RS 036
IS1 037
US 037
space ' '
exclamationmark '!'
quotation-mark '"'
number-sign '#'
dollar-sign '$'
percent-sign '%'
ampersand '&'
apostrophe '\''
left-parenthesis '('
right-parenthesis ')'
asterisk '*'
plus-sign '+'
comma ','
hyphen '-'
hyphen-minus '-'
period '.'
full-stop '.'
slash '/'
solidus '/'
zero '0'
one '1'
two '2'
three '3'
four '4'
five '5'
six '6'
seven '7'
eight '8'
nine '9'
colon ':'
semicolon ';'
less-than-sign '<'
equals-sign '='
greater-than-sign '>'
question-mark '?'
commercial-at '@'
left-square-bracket '['
backslash '\\'
reverse-solidus '\\'
right-square-bracket ']'
circumflex '^'
circumflex-accent '^'
underscore '_'
low-line '_'
grave-accent '`'
left-brace '{'
left-curly-bracket '{'
vertical-line '|'
right-brace '}'
right-curly-bracket '}'
tilde '~'
DEL 177
// use character for permissible character
mysql> SELECT '{expelle}' REGEXP '[[.{.]].*[[.}.]]';
+---------------------------------------+
| '{expelle}' REGEXP '[[.{.]].*[[.}.]]' |
+---------------------------------------+
| 1 |
+---------------------------------------+
1 row in set (0.00 sec)
// use name for permissible character
mysql> SELECT '{expelle}' REGEXP '[[.left-brace.]].*[[.right-brace.]]';
+----------------------------------------------------------+
| '{expelle}' REGEXP '[[.left-brace.]].*[[.right-brace.]]' |
+----------------------------------------------------------+
| 1 |
+----------------------------------------------------------+
1 row in set (0.00 sec)
