目录
一、第9章,正则匹配
二、实操
1、基本字符匹配
SELECT shipping_company FROM o_order WHERE shipping_company REGEXP "OOC"
通俗理解就是,shipping_company 包含“OOC”的都检索出来;这个用法有点像LIKE。
注意:REGEXP大小写不敏感;
返回:
2、 可以搭配OR使用
3、 匹配几个字符中的一个
1)、使用[]符号,可以是数字[1-9],也可是字母[a-z],当然还可以[145]显示的表示出来。
2)、As you have seen, [] is another form of |. The regular expression [123]000 is equal [1|2|3]000.
3)、还可以配合^来使用,表示否定。[^145]000 表示除了145开头的内容。
如:
4、匹配特殊字符 . [] |
使用转译字符 \\ 比如: REGEXP "\\."
5、匹配字符集
如果是为了匹配数字或者字母相关的,mysql提供了字符集,
Version:0.9 StartHTML:0000000105 EndHTML:0000006808 StartFragment:0000000141 EndFragment:0000006768
Class Description
[:alnum:]
Any letter or digit, (same as
[a-zA-Z0-9]
)
[:alpha:]
Any letter (same as
[a-zA-Z]
)
[:blank:]
Space or tab (same as
[\\t ]
)
[:cntrl:]
ASCII control characters (ASCII
0
tHRough
31
and
127
)
[:digit:]
Any digit (same as
[0-9]
)
[:graph:]
Same as
[:print:]
but excludes space
[:lower:]
Any lowercase letter (same as
[a-z]
)
[:print:]
Any printable character
[:punct:]
Any character that is neither in
[:alnum:]
nor
[:cntrl:]
[:space:]
Any whitespace character including the space (same as
[\\f\\n\\r\\t\\v ]
)
[:upper:]
Any uppercase letter (same as
[A-Z]
)
[:xdigit:]
Any hexadecimal digit (same as
[a-fA-F0-9]
)
比如:
6、匹配多个实例
在某些情况下,我们需要匹配任何包含数字的字符串,或者能够检索出某个字符串后面至少存在一个字符。这时候我们可以用上这些 重复特效字符
Metacharacter Description
*
0 or more matches
+
1 or more matches (equivalent to
{1,}
)
?
0 or 1 match (equivalent to
{0,1}
)
{n}
Specific number of matches
{n,}
No less than a specified number of matches
{n,m}
Range of matches (
m
not to exceed 255)
来个栗子:
REGEXP "sticks?" 那么会匹配 sticks 和stick。 ?的意思是前一个字符要出现零次或者1次;
REGEXP “法国长荣 [123]0000?” 表示000后面至少还要有零或者1个0
如下:
另外栗子如下:
7、其它字符
用于指定某些字符开头或者结尾
Metacharacter Description
^
Start of text
$
End of text
[[:<:]]
Start of word
[[:>:]]
End of word
例如: