SQL通配符及模式匹配

前提,MS SQL的通配符含义：

序号通配符含义示例1%包含零个或多个字符的任意字符串。  WHERE title LIKE '%computer%' 将查找在书名中任意位置包含单词"computer" 的所有书名。 2_任何单个字符。WHERE au_fname LIKE '_ean' 将查找以 ean 结尾的所有 4 个字母的名字（Dean、Sean 等）。
3[ ]指定范围 ([a-f]) 或集合 ([abcdef]) 中的任何单个字符。WHERE au_lname LIKE '[C-P]arsen' 将查找以 arsen 结尾并且以介于 C 与 P 之间的任何单个字符开始的作者姓氏，例如 Carsen、Larsen、Karsen 等。在范围搜索中，范围包含的字符可能因排序规则的排序规则而异。
4[^] 不属于指定范围 ([a-f]) 或集合 ([abcdef]) 的任何单个字符。WHERE au_lname LIKE 'de[^l]%' 将查找以 de 开始并且其后的字母不为 l 的所有作者的姓氏。

 

 

定义测试表：
create table EscapePattern(value varchar(100))goinsert into EscapePatternselect 'aSQL'union all select 'SQL'union all select 'SQLa'union all select '中SQL'union all select 'SQL中'union all select 'aSQLa'union all select '中SQL中'union all select 'SQaL'union all select 'SQ中L'union all select 'aSQaLa'union all select '中SQ中L中'union all select '中国_CN'union all select 'a%ddd%b'union all select '[]'union all select '[[]'union all select '[]]'union all select '[[]]'union all select '^-]'union all select 'a^-]a'

 

然后，我们进行逐个分析

1、我们知道，对于字符串，其开始和结束标识符为单引号，如果字符串里面有单引号，我们可以直接使用两个单引号来替代。
形如：空串  '', 单引号  '''', 字符串 'abAB#中国字符串', 包含单引号的串 'abAB#中国''字符串'.

2、'_' 、 '%'和'['
(1) '_'匹配单个字符，例:
    select * from EscapePattern where value like '_SQL' --查找四个字符，并且以'SQL'结束的记录
    结果为：aSQL 中SQL

    其中like语句还可以这样写：
    'SQL_' 查找四个字符，并且以'SQL'开始的记录，结果：SQLa SQL中
    'SQ_L' 查找四个字符，并且以'SQ'开始，以'L'结束的记录，结果：SQaL SQ中L
    '_SQL_' 查找五个字符，中间的三个字符是'SQL'，结果：aSQLa 中SQL中
    '_SQ_L_' 查找六个字符，第二个、第三个和第五个字符分别为'S'、'Q'和'L'的记录 aSQaLa 中SQ中L中
    其中'_'可以任意多个，无限制(当然别超过字符串的显示最大值，肯定会报错)。
(2) '%' 匹配任意多个字符(>=0)，使用方法同'_'，主要是不限制字符串的个数，例：
    select * from EscapePattern where value like '%SQL' -- 查找大于或等于3个字符，并以'SQL'结尾的记录
    其中like的其他写法：
    'SQL%' 查找大于等于3个字符，并且以'SQL'开始的记录
    'SQ%L' 查找大于等于3个字符，并且以'SQ'开始，以'L'结束的记录
    '%SQL%' 查找大于等于3个字符，必须包含'SQL'的记录
    '%SQ%L%' 查找大于等于3个字符，必须包含'SQ'和'L'，并且'SQ'在'L'前面的记录

    另外，如果要查询'_'或'%',可以使用'[]'包含起来，如'[_]'和'[%]'，这样，利用匹配样式，将其转换为普通字符。
    如select * from EscapePattern where value like '%[_]%'结果为:中国_CN，即包含'_'的记录。
(3) '['因为是模式匹配的开始字符，如果是查询此字面，而不是用在模式匹配时，也需要进行处理，使其成为普通字符'[[]'：如'['-->'[[]', '[[' --> '[[][[]'

3、在模式匹配(Pattern)中使用的字符:'^'、'-',']'，它们本来就包含在'[]'中，在外部，不需要进行处理，同普通字符，但如果在模式匹配里面，则需要引用其方法来进行查询，如果在Like里面，需要引入Escape进行转义：
如:

  select * from EscapePattern where value like 'a[/^]%' escape '/' -- 查询以'a^'开始的所有记录。
  select * from EscapePattern where value like '%[/]]%' escape '/' -- 查询所有包含']'字符的记录
  select * from EscapePattern where value like '%[/-]%' escape '/' -- 查询所有包含'-'字符的记录

这样一切都可以解决问题！

 

但是，另外一个问题出现了。如果只使用
PATINDEX ( '%pattern%' , expression )，无法使用Escape
如果有如果模式匹配里面使用了'-'、'^'或']'，可以这样来做:
(1) 对于'-'，可以放在最后或最前面如'[a-z-]'或'[-a]'，不在中间充当连接符(如数字[0-9]，小写字符[a-z]中的连接符)
(2) 对于'^'，由于其只在模式匹配最开始有效,形如'[XX^XX]'，所以，只要不放在最开始，什么地方都可以。
(3) 对于']', 可以将其不放在模式匹配里面，也可以将其替换成其他字符，然后再转换回来。

 

下面是一些比较有用的pattern，如果你知道正则表达式，那么就相当容易理解:
小写字母[a-z] 大写字母[A-Z] 数字[0-9] 汉字[吖-咗]
非小写字母[^a-z] 非大写字母[^A-Z] 非数字[^0-9] 非汉字[^吖-咗]