cast(字段 as varbinary)再比较 declare @a table(a nvarchar(10)) insert into @a select 'a' union all select 'A' select * from @a where cast(a as varbinary)=cast('a' as varbinary) 结果: ---------------- a ---------- a(所影响的行数为 1 行)
用代码说话: declare @str1 as varchar(10) declare @str2 as varchar(10) set @str1='A' set @str2='a'if cast(@str1 as varbinary)=cast(@str2 as varbinary) print '相等' else print '不等'
SQL中如何区分大小写 比如说一个表中两条记录的address字段值分别为:aaaa 和 aAAa,如果用 select * from mytable where address = 'aaaa' 则两条记录都查出来了,我希望只得到一条记录,这样的SQL应该怎么写?create table #a( [id] [char] (10), [address] [char] (10) ) insert into #a(id , address) values('1' , 'aaaa') insert into #a(id , address) values('1' , 'aAAa')select * from #a where address = 'aaaa' COLLATE Chinese_PRC_CS_AS select * from #a where address = 'aAAa' COLLATE Chinese_PRC_CS_ASdrop table #aid address ---------- ---------- 1 aaaa (所影响的行数为 1 行)id address ---------- ---------- 1 aAAa (所影响的行数为 1 行)如何查只以大写AB开头的呢? 通常情况下写select * from 表名 where 列名 like 'AB%' 但是这样,以小写ab开头的纪录也会被查找出来 如何查只以大写AB开头的呢? select * from table where left(col,2) = 'AB%' COLLATE Chinese_PRC_CS_AS select * from table where col like 'AB%' COLLATE Chinese_PRC_CS_AS
declare @a table(a nvarchar(10))
insert into @a select 'a' union all
select 'A'
select * from @a where cast(a as varbinary)=cast('a' as varbinary)
结果:
----------------
a
----------
a(所影响的行数为 1 行)
if 'a' collate Chinese_PRC_CS_AI='A'
print '相等'
else
print '不相等'
--result
不相等--不区分大小写,不区分重音
if 'a' collate Chinese_PRC_CI_AI='A'
print '相等'
else
print '不相等'
--result
相等
if 'a' collate Chinese_PRC_CS_AI='A'
print '相等'
else
print '不相等'
--result
不相等--不区分大小写,不区分重音
if 'a' collate Chinese_PRC_CI_AI='A'
print '相等'
else
print '不相等'
--result
相等
-------------------------
if 'a' collate Chinese_PRC_CS_AI='A'是什么意思呢?能解释一下吗?
一个子句,可应用于数据库定义或列定义以定义排序规则,或应用于字符串表达式以应用排序规则投影。
declare @str1 as varchar(10)
declare @str2 as varchar(10)
set @str1='A'
set @str2='a'if cast(@str1 as varbinary)=cast(@str2 as varbinary)
print '相等'
else
print '不等'
比如说一个表中两条记录的address字段值分别为:aaaa 和 aAAa,如果用
select * from mytable where address = 'aaaa'
则两条记录都查出来了,我希望只得到一条记录,这样的SQL应该怎么写?create table #a(
[id] [char] (10),
[address] [char] (10)
)
insert into #a(id , address) values('1' , 'aaaa')
insert into #a(id , address) values('1' , 'aAAa')select * from #a where address = 'aaaa' COLLATE Chinese_PRC_CS_AS
select * from #a where address = 'aAAa' COLLATE Chinese_PRC_CS_ASdrop table #aid address
---------- ----------
1 aaaa (所影响的行数为 1 行)id address
---------- ----------
1 aAAa (所影响的行数为 1 行)如何查只以大写AB开头的呢?
通常情况下写select * from 表名 where 列名 like 'AB%'
但是这样,以小写ab开头的纪录也会被查找出来 如何查只以大写AB开头的呢?
select * from table where left(col,2) = 'AB%' COLLATE Chinese_PRC_CS_AS
select * from table where col like 'AB%' COLLATE Chinese_PRC_CS_AS