SQL SERVER 转义字符和通配符

[文章] 浏览(14)  | 2018年04月11日  | 支持服务 | 
[标签]MS SQL

SQL Server中特殊字符问题:

执行:print replace(N'"ˈɑːkən',N'"','1')
结果:"ˈɑːkən    不能替换'"'(双引号),因其后是'ˈ'(重音符),不解!!
 
执行:print replace(N'"ˈɑːkən',N'"ˈ','1')
结果:"1ɑːkən    替换'"'(双引号)和其后是'ˈ'(重音符)
 
执行:print replace(N'"ˈɑːkən','"ˈ','1')
结果:"ˈɑːkən   不能替换'"'(双引号)和其后是'ˈ'(重音符),因为含有特殊字符的字符串前须加N
 
执行:print replace('"ˈɑːkən',N'"ˈ','1')
结果:"?ɑ?k?n  乱码!因为源字符串含有特殊字符的字符串前无N,造成源字符串本身运算时是乱码了
 
特殊字符,比如双引号后跟重音符,不能匹配过滤,采用:
select * from table Where ascii(substring(Phonogram,1,1))=34 or Phonogram is null  -- 列表有记录

update table set Phonogram = replace(Phonogram,N'"','') --去掉音标字段双引号
update table set Phonogram = replace(Phonogram,N'"ˈ',N'ˈ') --替换音标字段双引号后跟重音符 为 重音符
update table set Phonogram = replace(Phonogram,N'"ˌ',N'ˌ') --替换音标字段双引号后跟次重音符 为 次重音符

select * from table Where ascii(substring(Phonogram,1,1))=34 or Phonogram is null  -- 列表无记录,表示已清除了音标字段首尾双引号

1.使用ESCAPE关键字,定义转义符

在模式中,当转义符置于通配符之前时,该通配符就解释为普通字符。例如,要搜索在任意位置包含字符串   5%   的字符串,请使用:    
  ... WHERE ColumnA LIKE '%5/%%' ESCAPE '/'  
注:ESCAPE默认?是 '/' 
例: SELECT   *  FROM   finances WHERE description LIKE 'gs_'  ESCAPE   'S'    

2、通配符

通配符 含义
% 包含零个或更多字符的任意字符串
_ 任何单个字符
[ ] 指定范围(例如 [a-f])或集合(例如 [abcdef])内的任何单个字符
[^]或[!] 不在指定范围(例如 [^a - f])或集合(例如 [^abcdef])内的任何单个字符

实例:

通配符 含义 结果
like '5[%]' 匹配字符'5'和特殊字符 '%' 5%
like '[_]n' 匹配特殊字符 '_'和字符‘n' _n
like '[a-cdf]' 匹配指定的五个字母 a, b, c, d, or f
like '[[]' 匹配特殊字符 '[' [
like 'abc[_]d%' 匹配以‘abc'和任意一个字符后跟‘d'字母 打头的字符串 abc_d and abc_de
like 'abc[def]' 匹配以‘abc'后跟‘def'之一字母 abcd, abce, and abcf
like '[^1-9]'   0
like '[^1-9b-z]'   0, a
对于字符串中出现的特殊字符:'%','[','[]', '_' 可以使用 '[]' 把它们包含起来, 这样在匹配模式(pattern)中,它们就被当作普通字符对待了。

1. 用 like '[[]' 匹配特殊字符 '['

select 1 where '[ABCDE' like '[[]%'

2. 用 like ']' 匹配特殊字符 ']'

select 1 where ']ABCDE' like ']%'

3. 用 like '[[]]' 匹配特殊字符 '[]'

select 1 where '[]ABCDE' like '[[]]%%'

4. 用 like '[_]' 匹配特殊字符 '_'

select 1 where '_ABCDE' like '[_]%'

5. 用 like '[%]' 匹配特殊字符 '%'

select 1 where 'ABC%DE' like 'ABC[%]DE'

对于其他的特殊字符:'^', '-', ']' 因为它们本身在包含在 '[]' 中使用,所以需要用另外的方式来转义,于是就引入了 like 中的 escape 子句,另外值得注意的是:escape 可以转义所有的特殊字符。

select 1 where '^ABCDE' like '!^ABCDE' escape '!' select 1 where '-ABCDE' like '!-ABCDE' escape '!' select 1 where ']ABCDE' like '!]ABCDE' escape '!'  select 1 where '%ABCDE' like '\%ABCDE' escape '\' select 1 where '%ABCDE' like '!%ABCDE' escape '!' select 1 where '%ABCDE' like '#%ABCDE' escape '#' select 1 where '%ABCDE' like '@%ABCDE' escape '@'  select 1 where '[ABCDE' like '![ABCDE' escape '!' select 1 where ']ABCDE' like '!]ABCDE' escape '!' 

看出规律了吧,就是用 escape 后面紧跟着的字符来做转义字符。 escape 后面的字符相当于 C 语言字符串中的转义字符 '\'。

最后,看一个更加复杂的匹配

select 1 where '[^A-Z]ABCDE' like '\[\^A\-Z\]%' escape '\' 
附件说明
附件