SQLSERVER按字母查询问题？

Question

SQLSERVER按字母查询问题？ 
有汉字字母还有韩文。如何查询？ 
单汉字字母查询方式？ 单韩文查询方式？

Answer 1

按拼音排序
DECLARE @t TABLE(col varchar(2))
INSERT @t SELECT '中'
UNION ALL SELECT '国'
UNION ALL SELECT '人'SELECT * FROM @t ORDER BY col COLLATE Chinese_PRC_CS_AS_KS_WS
/*--结果
col  
---- 
国
人
中
--*/
GO

Answer 2

--按韩语的估计是:SELECT * FROM @t ORDER BY col COLLATE Korean_Wansung_CS_AS_KS_WS--以下为韩语的相关内容,自己再试试吧.
/*
name                                description
----------------------------------- ----- 
Korean_Wansung_BIN                  Korean-Wansung, binary sort
Korean_Wansung_CI_AI                Korean-Wansung, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive
Korean_Wansung_CI_AI_WS             Korean-Wansung, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensitive
Korean_Wansung_CI_AI_KS             Korean-Wansung, case-insensitive, accent-insensitive, kanatype-sensitive, width-insensitive
Korean_Wansung_CI_AI_KS_WS          Korean-Wansung, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitive
Korean_Wansung_CI_AS                Korean-Wansung, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive
Korean_Wansung_CI_AS_WS             Korean-Wansung, case-insensitive, accent-sensitive, kanatype-insensitive, width-sensitive
Korean_Wansung_CI_AS_KS             Korean-Wansung, case-insensitive, accent-sensitive, kanatype-sensitive, width-insensitive
Korean_Wansung_CI_AS_KS_WS          Korean-Wansung, case-insensitive, accent-sensitive, kanatype-sensitive, width-sensitive
Korean_Wansung_CS_AI                Korean-Wansung, case-sensitive, accent-insensitive, kanatype-insensitive, width-insensitive
Korean_Wansung_CS_AI_WS             Korean-Wansung, case-sensitive, accent-insensitive, kanatype-insensitive, width-sensitive
Korean_Wansung_CS_AI_KS             Korean-Wansung, case-sensitive, accent-insensitive, kanatype-sensitive, width-insensitive
Korean_Wansung_CS_AI_KS_WS          Korean-Wansung, case-sensitive, accent-insensitive, kanatype-sensitive, width-sensitive
Korean_Wansung_CS_AS                Korean-Wansung, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive
Korean_Wansung_CS_AS_WS             Korean-Wansung, case-sensitive, accent-sensitive, kanatype-insensitive, width-sensitive
Korean_Wansung_CS_AS_KS             Korean-Wansung, case-sensitive, accent-sensitive, kanatype-sensitive, width-insensitive
Korean_Wansung_CS_AS_KS_WS          Korean-Wansung, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitive
Korean_Wansung_Unicode_BIN          Korean-Wansung-Unicode, binary sort
Korean_Wansung_Unicode_CI_AI        Korean-Wansung-Unicode, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive
Korean_Wansung_Unicode_CI_AI_WS     Korean-Wansung-Unicode, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensitive
Korean_Wansung_Unicode_CI_AI_KS     Korean-Wansung-Unicode, case-insensitive, accent-insensitive, kanatype-sensitive, width-insensitive
Korean_Wansung_Unicode_CI_AI_KS_WS  Korean-Wansung-Unicode, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitive
Korean_Wansung_Unicode_CI_AS        Korean-Wansung-Unicode, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive
Korean_Wansung_Unicode_CI_AS_WS     Korean-Wansung-Unicode, case-insensitive, accent-sensitive, kanatype-insensitive, width-sensitive
Korean_Wansung_Unicode_CI_AS_KS     Korean-Wansung-Unicode, case-insensitive, accent-sensitive, kanatype-sensitive, width-insensitive
Korean_Wansung_Unicode_CI_AS_KS_WS  Korean-Wansung-Unicode, case-insensitive, accent-sensitive, kanatype-sensitive, width-sensitive
Korean_Wansung_Unicode_CS_AI        Korean-Wansung-Unicode, case-sensitive, accent-insensitive, kanatype-insensitive, width-insensitive
Korean_Wansung_Unicode_CS_AI_WS     Korean-Wansung-Unicode, case-sensitive, accent-insensitive, kanatype-insensitive, width-sensitive
Korean_Wansung_Unicode_CS_AI_KS     Korean-Wansung-Unicode, case-sensitive, accent-insensitive, kanatype-sensitive, width-insensitive
Korean_Wansung_Unicode_CS_AI_KS_WS  Korean-Wansung-Unicode, case-sensitive, accent-insensitive, kanatype-sensitive, width-sensitive
Korean_Wansung_Unicode_CS_AS        Korean-Wansung-Unicode, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive
Korean_Wansung_Unicode_CS_AS_WS     Korean-Wansung-Unicode, case-sensitive, accent-sensitive, kanatype-insensitive, width-sensitive
Korean_Wansung_Unicode_CS_AS_KS     Korean-Wansung-Unicode, case-sensitive, accent-sensitive, kanatype-sensitive, width-insensitive
Korean_Wansung_Unicode_CS_AS_KS_WS  Korean-Wansung-Unicode, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitive（所影响的行数为 34 行）
*/

Answer 3

如果数据库中多数是韩文的，那么就修改SQL的默认排序规则为Korean_Wansung_CI_AS_KS吧，这样就可以按照韩文的字幕字典顺序排序了

Answer 4

--1. 为数据库指定排序规则
CREATE DATABASE db COLLATE Chinese_PRC_CI_AS
GOALTER DATABASE db COLLATE Chinese_PRC_BIN
GO
/*====================================*/
--2. 为表中的列指定排序规则
CREATE TABLE tb(
col1 varchar(10),
col2 varchar(10) COLLATE Chinese_PRC_CI_AS)
GOALTER TABLE tb ADD col3 varchar(10) COLLATE Chinese_PRC_BIN
GOALTER TABLE tb ALTER COLUMN col2 varchar(10) COLLATE Chinese_PRC_BIN
GO
/*====================================*/
--3. 为字符变量和参数应用排序规则
DECLARE @a varchar(10),@b varchar(10)
SELECT @a='a',@b='A'--使用排序规则 Chinese_PRC_CI_AS
SELECT CASE WHEN @a COLLATE Chinese_PRC_CI_AS = @b THEN '@a=@b' ELSE '@a<>@b' END
--结果:@a=@b--使用排序规则 Chinese_PRC_BIN
SELECT CASE WHEN @a COLLATE Chinese_PRC_BIN = @b THEN '@a=@b' ELSE '@a<>@b' END
--结果:@a<>@b