sql server通配符查询

通配符_

"_"号表示任意单个字符,该符号只能匹配一个字符."_"可以放在查询条件的任意位置,且只能代表一个字符.一个汉字只使用一个"_"表示.

通配符%

"%"符号是字符匹配符,能匹配0个或更多字符的任意长度的字符串.在SQL语句中可以在查询条件的任意位置放置一个%来代表一个任意长度的字符串.在查询条件时也可以放置两个%进行查询,但在查询条件中最好不要连续出现两个%

通配符[]

在模式查询中可以利用"[ ]"来实现查询一定范围的数据.[ ]用于指定一定范围内的任何单个字符,包括两端数据

LIKE '5[%]'      表示    5%

LIKE '[_]n'      表示    _n

LIKE '[a-cdf]'   表示    a、b、c、d或f

LIKE '[-acdf]'   表示    -、a、c、d或f

LIKE '[ [ ]'       表示    [

LIKE '[ ] ]'       表示    ]

LIKE 'abc[_]d%'  表示    abc_d开头的字符串(包括abc_d)

LIKE 'abc[def]'  表示    abcd、abce或abcf

通配符[^]

[^]用来查询不属于指定范围 ([a-f]) 或集合 ([abcdef]) 的任何单个字符。

如:select * from alluser

where username like 'M[^abc]%'

表示从表alluser中查询用户名以M开头,且第二个字符不是a,b,c信息.

ESCAPE子句的模式匹配

可搜索包含一个或多个特殊通配符的字符串。例如，customers 数据库中的 discounts 表可能存储含百分号 (%) 的折扣值。若要搜索作为字符而不是通配符的百分号，必须提供 ESCAPE 关键字和转义符。例如，一个样本数据库包含名为 comment 的列，该列含文本 30%。若要搜索在 comment 列中的任何位置包含字符串 30% 的任何行，请指定由 Where comment LIKE '%30!%%' ESCAPE '!' 组成的 Where 子句。如果不指定 ESCAPE 和转义符，SQL Server 将返回所有含字符串 30 的行。

下例说明如何在 pubs 数据库 titles 表的 notes 列中搜索字符串"50% off when 100 or more copies are purchased"：

Select notes FROM titles

Where notes LIKE '50%% off when 100 or more copies are purchased'

   ESCAPE '%'

escape的主要用途

1.使用 ESCAPE 关键字定义转义符。 在模式中，当转义符置于通配符之前时，该通配符就解释为普通字符。例如，要搜索在任意位置包含字符串 5% 的字符串，请使用： Where ColumnA LIKE '%5/%%' ESCAPE '/'

2.ESCAPE 'escape_character' 允许在字符串中搜索通配符而不是将其作为通配符使用。 escape_character 是放在通配符前表示此特殊用途的字符。

Select * FROM finances Where descrīption LIKE 'gs_' ESCAPE 'S'

意思就是： 比如，我们要搜索一个字符串 "g_" ,如果直接 like "g_"，那么 "_"的作用就是通配符，而不是字符，结果，我们会查到比如 "ga","gb","gc",而不是我们需要的 "g_". 用 LIKE 'gs_' ESCAPE 'S' 's'表示特殊用法标志。

===============================

SQL Server和Oracle的查询通配符有所区别，%功能相同，Oracle中使用的？在SQLSERVER中以下划线'_'取代。另外还有方括号[]等，用法大致相同。

Wildcard character	Description	Example
%	Any string of zero or more characters.	WHERE title LIKE '%computer%' finds all book titles with the word 'computer' anywhere in the book title.
_ (underscore)	Any single character.	WHERE au_fname LIKE '_ean' finds all four-letter first names that end with ean (Dean, Sean, and so on).
[ ]	Any single character within the specified range ([a-f]) or set ([abcdef]).	WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen and starting with any single character between C and P, for example Carsen, Larsen, Karsen, and so on.
[^]	Any single character not within the specified range ([^a-f]) or set ([^abcdef]).	WHERE au_lname LIKE 'de[^l]%' all author last names starting with de and where the following letter is not l.

 

 

有时候，应用程序需要提供对通配符本身的搜索(literally search)，各家DB vendor就采用了不同的策略和实现，下面是SQLSERVER的做法：
  

Literal	Verification	Symbol
%	If(@inputTerm contains (‘%’,’_’))	LIKE ‘[%]’
_	If(@inputTerm contains (‘%’,’_’))	LIKE ‘[_]’
[ ]	If(@inputTerm contains ('[]'))	LIKE ‘![!]’ ESCAPE ‘!’
[^]	If(@inputTerm contains ('[^]'))	LIKE ‘![!^!]’ ESCAPE ‘!’

看到了吧，第一，二种都能看懂。看到后两个，LIKE后面出现了ESCAPE关键字，这是什么意思？
简单的说，就是SQLSERVER允许你自己“创造”通配符，我这里用了！作为我的自定义符号，用ESCAPE标识，编译过之后，引擎做LIKE句式时就会把！作为新的“单字符替代”通配符。

======================

1. 通配符"_"（单个字符）
"_"号表示任意单个字符，该符号只能匹配一个字符。"_"可以放在查询条件的任意位置，且只能代表一个字符，一个汉字只使用一个"_"表示。

2. 通配符"%"（0个或多个字符）：
"%"符号是字符匹配符，能匹配0个或更多字符的任意长度的字符串。在SQL语句中可以在查询条件的任意位置放置一个"%"来代表一个任意长度的字符串。在查询条件时也可以放置两个"%"进行查询，但在查询条件中最好不要连续出现两个"%"。

3. 通配符"[]"
在模式查询中可以利用"[]"来实现查询一定范围的数据。"[]"用于指定一定范围内的任何单个字符，包括两端数据。

4. 通配符"[^]"
"[^]"用来查询不属于指定范围 ([a-f]) 或集合 ([abcdef]) 的任何单个字符。
如:select * from user where username like 'M[^abc]%'
表示从表user中查询用户名以M开头，且第二个字符不是a，b，c信息。

=======================

Like特殊情况：搜索通配符字符

上面的搜索可以针对普通的汉字或中文，那如果遇到上述四种通配符要被搜到时应该如何处理呢？首先需注意的是通配符字符可以搜索，并且有两种方法可指定平常用作通配符的字符：

使用 ESCAPE 关键字定义转义符。在模式中，当转义符置于通配符之前时，该通配符就解释为普通字符。例如，要搜索在任意位置包含字符串 5% 的字符串，请使用：

WHERE ColumnA LIKE '%5/%%' ESCAPE '/'

在上述 LIKE 子句中，前导和结尾百分号 (%) 解释为通配符，而斜杠 (/) 之后的百分号解释为字符%。

在方括号 ([ ]) 中只包含通配符本身。要搜索破折号 (-) 而不是用它指定搜索范围，请将破折号指定为方括号内的第一个字符：

WHERE ColumnA LIKE '9[-]5'

下表显示了括在方括号内的通配符的用法。

符号 含义

LIKE '5[%]' 5%

LIKE '5%' 5 后跟 0 个或更多字符的字符串

LIKE '[_]n' _n

LIKE '_n' an, in, on (and so on)

LIKE '[a-cdf]' a, b, c, d, or f

LIKE '[-acdf]' -, a, c, d, or f

LIKE '[ [ ]' [

LIKE ']' ]

如果使用 LIKE 进行字符串比较，模式字符串中的包括起始空格和/或尾随空格在内的所有字符都有意义。如果查询比较要求返回包含"abc "（abc 后有一个空格）的所有行，则不会返回列值为"abc"（abc 后没有空格）行。但是反过来，情况并非如此。可以忽略模式所要匹配的表达式中的末尾空格。如果查询比较要求返回包含"abc"（abc 后没有空格）的所有行，则将返回以"abc"开始且具有零个或多个末尾空格的所有行。