(转)由SQL的like说开去

ps：很偶然的机会看到此文，可看过之后觉得还是很有用，贴来此处备案。

要选取表tbl中字段fld以"t_"开头的记录，使用"select * from tbl where fld like 't_%'"，然而结果却不如我们的意，它选出的是所有以"t"开头的记录，而不是以"t_"开头的记录。

原来"_"是通配符，表示零个或一个任何字符。虽然我们都知道"_"是个通配符，但在这里使用时往往会疏忽，尤其是关键词来自客户端输入时。这里正确的写法应该是"select * from tbl where fld lik 't[_]%'"。

"[]"表指定范围 (如[a-f]) 或集合 (如[abcdef]) 中的任何单个字符。
    "[^]"表不属于指定范围 (如[a-f]) 或集合 (如[abcdef]) 的任何单个字符。

那么我们不想将"["、"]"作为通配符时又如何写呢？
　　搜索"["用 like '[[]'
　　搜索"]"用 like ']'
　　比如我们要选取表tbl中字段fld含有"[1]"的记录，使用"select * from tbl where fld like '%[[]1]%'"。

当使用 LIKE 进行字符串比较时，模式字符串中的所有字符都有意义，包括起始或尾随空格。如果查询中的比较要返回包含"abc "（abc 后有一个空格）的所有行，则将不会返回包含"abc"（abc 后没有空格）的列所在行。但是可以忽略模式所要匹配的表达式中的尾随空格。如果查询中的比较要返回包含"abc"（abc 后没有空格）的所有行，则将返回以"abc"开始且具有零个或多个尾随空格的所有行。

假设表tbl中含有字段fld（varchar类型），表共有四条记录，各记录的fld值分别是："abc　"（abc后有一中文空格），"abc "（abc后有一英文空格），"abc"，" abc"（abc前一有英文空格）。那么：
　　select * from tbl where fld like 'abc'，将取出前三条记录，记录中的尾随空格被忽略。
　　select * from tbl where fld like 'abc　'（abc后有一中文空格），那么将取出前两条记录，模式字符串中的尾随空格不能忽略。
　　select * from tbl where fld like 'abc '（abc后有一英文空格），那么将取出前两条记录。
　　select * from tbl where fld like ' abc'（abc前有一中文空格），那么将取出第四条记录。

如果上例中fld字段类型为char(20)，那么第二、三条SQL语句将取出前三条记录，因为char类型字段会在记录后以空格补足长度，其它一样。

在前面我们介绍搜索通配符字符时，使用"[]"，比如要搜索"_"字符，就用"[_]"，这里介绍另一种方法——自定义转义符。我们知道要搜索英文单引号（"'"），我们用两个英文单引号，其中第一个就是转义符。在SQL中，自定义转义符的关键字是escape。

比如我们要选取表tbl中字段fld含有"1_"的记录，使用"select * from tbl where fld like '%1/_%' escape '/'"，这样就把'/'当作转义符了，"_"也就不会当作通配符了。

转义符忽略大小写吗？
　　不！如下两句结果是不一样的：
　　select * from tbl where fld like '%1d_%' escape 'd'
　　select * from tbl where fld like '%1D_%' escape 'd'

两个转义字符在一起，两个字符都是转义字符吗？
　　不！看下面的例子：
　　select * from tbl where fld like '%1ddd_%' escape 'd'
　　它搜索fld含有"1d_"的记录。

与UNICODE字符运算
　　表tbl字段fld类型为nvarchar(20)
　　declare @key varchar(20)
　　set @key = 'abc'
　　select * from tbl where fld like '%'+@key+'%'
　　会选取出字段fld含有'abc'的记录。

与float类型运算
　　我们知道float存储的是近似值，假如值3.0，可能是按2.99999...来存储的，但这只是它的存储方面，在使用like比较之前，它会按存储的值转换成实际的float值，然后再转换成varchar值。所以这时我们使用like '2.99%'和like '3.0'都找不到这条记录的。应该用like '3'。

与datetime类型运算
　　同样，是先将存储的值转换成datetime格式，再转换成varchar，然后进行like运算。
　　比如将2005-03-04 11:21:49转换成varchar为03  4 2005 11:21AM，我们就可以进行like运算。smalldatetime也是一样的。

与null值
　　试图用"select * from tbl where fld like null"来提取表tbl中字段fld为null的记录是错误的用法，因为有null参与的运算同样返回null，我们应该用"select * from tbl where fld is null"。