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]) 的任何单个字符。
"[^]"表不属于指定范围 (如[a-f]) 或集合 (如[abcdef]) 的任何单个字符。
那么我们不想将"["、"]"作为通配符时又如何写呢?
搜索"["用 like '[[]'
搜索"]"用 like ']'
比如我们要选取表tbl中字段fld含有"[1]"的记录,使用"select * from tbl where fld like '%[[]1]%'"。
搜索"["用 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前有一中文空格),那么将取出第四条记录。
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 '%1d_%' escape 'd'
select * from tbl where fld like '%1D_%' escape 'd'
两个转义字符在一起,两个字符都是转义字符吗?
不!看下面的例子:
select * from tbl where fld like '%1ddd_%' escape 'd'
它搜索fld含有"1d_"的记录。
不!看下面的例子:
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'的记录。
表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'。
我们知道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值同样,是先将存储的值转换成datetime格式,再转换成varchar,然后进行like运算。
比如将2005-03-04 11:21:49转换成varchar为03 4 2005 11:21AM,我们就可以进行like运算。smalldatetime也是一样的。
试图用"select * from tbl where fld like null"来提取表tbl中字段fld为null的记录是错误的用法,因为有null参与的运算同样返回null,我们应该用"select * from tbl where fld is null"。