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 新字符集
改的时候要关闭其他连接!