让SQLServer like查询大小写敏感

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