如何使用SQL Server校勘功能
校勘(collation)是指对代码页、字母大小写、音调、语言和字母表的整理,很多校勘都是在数据进入数据库之前进行的,根据我的经验,北美的大部分数据库管理员都在使用默认的大小写敏感的校勘设定,这对于数据本身没有影响,但是对于数据的比较和索引会造成影响。
这篇文章将向您介绍一些相关的校勘方法,以及数据库对多种字符集的处理方法。
为了说明校勘的使用,我们首先来创建一个数据库,列表A给出了创建数据库的代码。
CREATESCHEMACaseCheckGOCREATETABLECaseCheck.Names(NamePKIntIdentity(1,1)PRIMARYKEY,NameVarchar(50)NOTNULL)GOINSERTINTOCaseCheck.NamesVALUES('Fuller')INSERTINTOCaseCheck.NamesVALUES('FuLLer')INSERTINTOCaseCheck.NamesVALUES('FULLER')INSERTINTOCaseCheck.NamesVALUES('fuller')GO
列表A
列表B是一些查询,对于大小写不敏感的情况,这几个查询将返回相同的四个数据行,但是如果我们的任务是从数据行中选取大小写完全匹配的记录呢?
SELECT*FROMCaseCheck.NamesSELECT*FROMCaseCheck.NamesWHEREName='fuller'SELECT*FROMCaseCheck.NamesWHEREName='FULLER'SELECT*FROMCaseCheck.NamesWHEREName='fuller'GO
列表B
现在我们就要使用校勘的功能了,尽管在数据库创建的时候,数据库管理员已经指定了校勘的选项,但是您可以通过WHERE语句中的COLLATE选项来使用不同的校勘设定,在列表C的查询中,前三个都返回了一条记录,而最后一个查询的返回结果为空。
SELECT*FROMCaseCheck.NamesWHEREName='fuller'COLLATELatin1_General_CS_ASSELECT*FROMCaseCheck.NamesWHEREName='FULLER'COLLATELatin1_General_CS_ASSELECT*FROMCaseCheck.NamesWHEREName='fuller'COLLATELatin1_General_CS_ASSELECT*FROMCaseCheck.NamesWHEREName='FUllER'COLLATELatin1_General_CS_ASGO
列表C
假设您现在想把所有'fuller'形式的拼写改为'Fuller'(包括像'fUlLEr'这种毫无意义的拼写组合),通过列表D中的代码,您可以非常轻松地实现这一功能。
UPDATECaseCheck.NamesSETName=UPPER(LEFT(Name,1))+LOWER(SUBSTRING(Name,2,LEN(Name)-1))
列表D
从大型主机上导入数据的时候,我都会首先运行以上的代码,这样就不必再担心单词大小写的问题了。
如果您想查看所有的校勘选项及其简要说明,可以运行以下的SQL代码:SELECT*FROM::fn_helpcollations()。
想要查看数据库当前的校勘设定,可以使用以下代码:
SELECTcollation_nameFROMmaster.sys.databasesWHEREName='SQLTips'
在列表E中,我添加了一些带有特殊字符的数据行,我使用了我的好朋友DejanSunderic的名字,他的名字应该拼写为Šunderic'(字母c实际上应该有重音符号的,由于使用的字体,所以显示成了现在的效果)。
INSERTINTOCaseCheck.NamesVALUES(NChar(352)+'underi'+NChar(263))INSERTINTOCaseCheck.NamesVALUES('SUNDERIC')INSERTINTOCaseCheck.NamesVALUES('sUnDeRI'+Nchar(263))INSERTINTOCaseCheck.NamesVALUES('Sunderi'+Nchar(263))GO
列表E
如果不使用COLLATE,那么会发生非常有意思的事情,运行一下的查询语句:
SELECT*FROMCaseCheck.NamesWHERENameLIKEN'S%'
您得到的结果是:
NamePKName6SUNDERIC7sUnDeRIc8Sunderic注意,带有特殊字符的'Šunderic'并没有被包含在内,让我们再来看看另外一个实验:SELECT*FROMCaseCheck.NamesORDERBYName
运行此查询的结果如下:
NamePKName5Šunderic1Fuller2FuLLer3FULLER4fuller6SUNDERIC7sUnDeRIc
这里有很明显的错误,PK5应当位于'Fuller'之后,我不是语言专家,因此不知道它应该位于Ss的什么位置,但是我觉得这是本地规则造成的结果,很明显,'Šunderic'应该出现在所有的'Fuller'之后,而不是在'Fuller'前面。
我还进行了另外一项实验,使用西里尔字母来拼写我的好朋友AlexanderKarmanov的名字,在图片A中您可以看到西里尔字母的拼写,我运行了图片B所示的语句来添加这条记录。
图片A
图片B
图片C
使用默认的字符集,此查询将会返回一系列问号,如果您想得到正确的结果,那么必须需要使用数据库可以接受的Unicode字符集,参见图片C。
如果您想确认一个给定数据库中使用的初始校勘,可以使以下的代码:
SELECTcollation_nameFROMmaster.sys.databasesWHEREName='SQLTips'