SQL SERVER 查询通配符小议

  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句式时就会把！作为新的“单字符替代”通配符。看懂了吧？^_^