MySQL学习笔记之正则表达式

Regular Expressions

SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name;
# Retriveves all rows where column prod_name contains the text 1000

LIKE matches the whole colunm value while REGEXP matches within column values.

SELECT prod_name
FROM products
WHERE prod_name REGEXP '.000'
ORDER BY prod_name;

SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000|2000'
ORDER BY prod_name; 

Matches are NOT case-sensitive.

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[123] Ton'
ORDER BY prod_name

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[1-5] Ton'
ORDER BY prod_name;

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[^2-4] ton';

SELECT vend_name
FROM vendors
WHERE vend_name REGEXP '\\.';
# Attach \\ when matches special characters

Metacharacter	Description
\\f	Form feed
\\n	Line feed
\\r	Carrige return
\\t	Tab
\\v	Vertical tab
\\\	\

Character Classes

Class	Description
[:alnum:]	Any letter or digit
[:alpha:]	Any letter
[:blank:]	Space or tab
[:cntrl:]	ASCII control characters ASCII 0-31, 127
[:digit:]	Any digit
[:print:]	Any printable character
[:graph:]	Same as [:print:] but excludes space
[:lower:] [:upper:]
[:space:]
[:xdigit:]	Any hexadecimal digit

Repetition Metacharacters

Metacharacter	Description
*	0 or more matches
+	1 or more mathces
?	0 or 1 mathces
{n}	n mathces
{n,}	no less than n of matches
{n,m}	range of matches(m <= 255)

SELECT prod_name
FROM products
WHERE prod_name REGEXP '\\([:digit:] sticks?\\)'
ORDER BY prod_name;

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[:digit:]{4}'
ORDER BY prod_name;

Anchors

Metacharacter	Description
^	Start of text
$	End of text
[[:<:]]	Start of word
[[:>:]]	End of word

Find all products started with a number(includes .)

SELECT prod_name
FROM products
WHERE prod_name REGEXP '^[0-9\\.]'
ORDER BY prod_name;