如何让sqlserver对查询字符串大小写敏感

select * from tableName where name like '%c%'
要选出只包括小写c的name

如何实现?
---------------------------------------------------------------

CREATE TABLE [Table7] (
[string] [varchar] (50) COLLATE Chinese_PRC_CI_AI NULL ,
[curdate] [datetime] NOT NULL CONSTRAINT [DF_Table7_curdate] DEFAULT (getdate())
) ON [PRIMARY]
GO

insert table7(string,curdate) values( 'a12' , '2002-08-27 17:21:05.140' )
insert table7(string,curdate) values( 'A21' , '2002-08-27 17:21:07.433' )
insert table7(string,curdate) values( 'aaa' , '2002-08-27 17:21:18.097' )
insert table7(string,curdate) values( 'Asss' , '2002-08-27 17:21:23.917' )
insert table7(string,curdate) values( 'sAs' , '2002-08-27 17:21:26.140' )
insert table7(string,curdate) values( 'aaaaaq' , '2002-08-27 17:21:28.240' )
insert table7(string,curdate) values( 'xxxa' , '2002-08-27 17:21:32.307' )
insert table7(string,curdate) values( '121A' , '2002-09-05 14:34:11.910' )
go

SELECT *
FROM Table7

string curdate
-------------------------------------------------- ------------------------------------------------------
a12 2002-08-27 17:21:05.140
A21 2002-08-27 17:21:07.433
aaa 2002-08-27 17:21:18.097
Asss 2002-08-27 17:21:23.917
sAs 2002-08-27 17:21:26.140
aaaaaq 2002-08-27 17:21:28.240
xxxa 2002-08-27 17:21:32.307
121A 2002-09-05 14:34:11.910

(8 row(s) affected)

--字符集是Chinese_PRC_CI_AI,大小写不敏感

SELECT *
FROM Table7
WHERE (string LIKE '%a%')

string curdate
-------------------------------------------------- ------------------------------------------------------
a12 2002-08-27 17:21:05.140
A21 2002-08-27 17:21:07.433
aaa 2002-08-27 17:21:18.097
Asss 2002-08-27 17:21:23.917
sAs 2002-08-27 17:21:26.140
aaaaaq 2002-08-27 17:21:28.240
xxxa 2002-08-27 17:21:32.307
121A 2002-09-05 14:34:11.910

(8 row(s) affected)

--改变字符集

alter table table7 ALTER COLUMN string varchar(50) COLLATE Chinese_PRC_CS_AI
go

--字符集是Chinese_PRC_CS_AI,大小写敏感

SELECT *
FROM Table7
WHERE (string LIKE '%a%')

string curdate
-------------------------------------------------- ------------------------------------------------------
a12 2002-08-27 17:21:05.140
aaa 2002-08-27 17:21:18.097
aaaaaq 2002-08-27 17:21:28.240
xxxa 2002-08-27 17:21:32.307

(4 row(s) affected)

--改变字符集

alter table table7 ALTER COLUMN string varchar(50) COLLATE Chinese_PRC_CI_AI
go

--字符集是Chinese_PRC_CI_AI,大小写不敏感

string curdate
-------------------------------------------------- ------------------------------------------------------
a12 2002-08-27 17:21:05.140
A21 2002-08-27 17:21:07.433
aaa 2002-08-27 17:21:18.097
Asss 2002-08-27 17:21:23.917
sAs 2002-08-27 17:21:26.140
aaaaaq 2002-08-27 17:21:28.240
xxxa 2002-08-27 17:21:32.307
121A 2002-09-05 14:34:11.910

(8 row(s) affected)

字符集信息
Chinese_PRC_CI_AI Chinese-PRC, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive

Chinese_PRC_CS_AI Chinese-PRC, case-sensitive, accent-insensitive, kanatype-insensitive, width-insensitive

检索 Windows 排序规则和 SQL 排序规则的有效排序规则名列表
SELECT *
FROM ::fn_helpcollations()

改变数据库字符集
ALTER DATABASE test COLLATE Japanese_CS_AI

---------------------------------------------------------------

就是 duckcn(duck)说的意思,改代码页,可以改一个字段的,也可以改整个库的。

呵呵,上面打错了,代码页写成了代码也。

---------------------------------------------------------------

请严格按照我下面的步骤来:
c:>net stop mssqlserver
停掉SQL Server的服务.
c:>\\mssql7\binn\rebuildm.exe
运行rebuildm.exe文件已重建master库,更改字符集,排序方式,及大小写敏感.
(它不是要你重装,不要搞错了!!!)

重建好后
c:>net start mssqlserver
启动SQL Server的服务.

---------------------------------------------------------------
ALTER DATABASE yourdb COLLATE 新字符集

改的时候要关闭其他连接!

Published At
Categories with 数据库类
Tagged with
comments powered by Disqus