SQL Server 排序规则

4.1 排序规则在拼音处理中的应用

--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

/*==========================================*/

--2. 汉字首字母查询处理用户定义函数
CREATE FUNCTION f_GetPY(@str nvarchar(4000))
RETURNS nvarchar(4000)
AS
BEGIN
    DECLARE @py TABLE(
        ch char(1),
        hz1 nchar(1) COLLATE Chinese_PRC_CS_AS_KS_WS,
        hz2 nchar(1) COLLATE Chinese_PRC_CS_AS_KS_WS)
    INSERT @py SELECT 'A',N'吖',N'鏊'
    UNION  ALL SELECT 'B',N'八',N'簿'
    UNION  ALL SELECT 'C',N'嚓',N'错'
    UNION  ALL SELECT 'D',N'哒',N'跺'
    UNION  ALL SELECT 'E',N'屙',N'贰'
    UNION  ALL SELECT 'F',N'发',N'馥'
    UNION  ALL SELECT 'G',N'旮',N'过'
    UNION  ALL SELECT 'H',N'铪',N'蠖'
    UNION  ALL SELECT 'J',N'丌',N'竣'
    UNION  ALL SELECT 'K',N'咔',N'廓'
    UNION  ALL SELECT 'L',N'垃',N'雒'
    UNION  ALL SELECT 'M',N'妈',N'穆'
    UNION  ALL SELECT 'N',N'拿',N'糯'
    UNION  ALL SELECT 'O',N'噢',N'沤'
    UNION  ALL SELECT 'P',N'趴',N'曝'
    UNION  ALL SELECT 'Q',N'七',N'群'
    UNION  ALL SELECT 'R',N'蚺',N'箬'
    UNION  ALL SELECT 'S',N'仨',N'锁'
    UNION  ALL SELECT 'T',N'他',N'箨'
    UNION  ALL SELECT 'W',N'哇',N'鋈'
    UNION  ALL SELECT 'X',N'夕',N'蕈'
    UNION  ALL SELECT 'Y',N'丫',N'蕴'
    UNION  ALL SELECT 'Z',N'匝',N'做'
    DECLARE @i int
    SET @i=PATINDEX('%[吖-做]%' COLLATE Chinese_PRC_CS_AS_KS_WS,@str)
    WHILE @i>0
        SELECT @str=REPLACE(@str,SUBSTRING(@str,@i,1),ch)
            ,@i=PATINDEX('%[吖-做]%' COLLATE Chinese_PRC_CS_AS_KS_WS,@str)
        FROM @py
        WHERE SUBSTRING(@str,@i,1) BETWEEN hz1 AND hz2
    RETURN(@str)
END
GO
 

4.2 排序规则在全角与半角处理中的应用

--1．查询区分全角与半角字符
--测试数据
DECLARE @t TABLE(col varchar(10))
INSERT @t SELECT 'aa'
UNION ALL SELECT 'Aa'
UNION ALL SELECT 'ＡＡ'      --全角Ａ
UNION ALL SELECT 'Ａ,Ａ'        --全角Ａ,半角逗号(,)
UNION ALL SELECT 'Ａ，Ａ'    --全角Ａ,全角逗号(，)

--1.查大写字母
SELECT * FROM @t
WHERE col COLLATE Chinese_PRC_CS_AS_WS like '%A%'

--2.查全角字母
SELECT * FROM @t
WHERE col COLLATE Chinese_PRC_CS_AS_WS like '%Ａ%'

--3.查半角逗号(,)
SELECT * FROM @t
WHERE col COLLATE Chinese_PRC_CS_AS_WS like '%,%'

--3.查全角逗号(，)
SELECT * FROM @t
WHERE col COLLATE Chinese_PRC_CS_AS_WS like '%，%'
GO

/*=============================================*/

--2 实现全角与半角字符转换的处理函数
CREATE FUNCTION f_Convert(
@str NVARCHAR(4000), --要转换的字符串
@flag bit              --转换标志,0转换成半角,1转换成全角
)RETURNS nvarchar(4000)
AS
BEGIN
    DECLARE @pat nvarchar(8),@step int,@i int,@spc int
    IF @flag=0
        SELECT @pat=N'%[！-～]%',@step=-65248,
            @str=REPLACE(@str,N'　',N' ')
    ELSE
        SELECT @pat=N'%[!-~]%',@step=65248,
            @str=REPLACE(@str,N' ',N'　')
    SET @i=PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN,@str)
    WHILE @i>0
        SELECT @str=REPLACE(@str,
                SUBSTRING(@str,@i,1),
                NCHAR(UNICODE(SUBSTRING(@str,@i,1))+@step))
            ,@i=PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN,@str)
    RETURN(@str)
END
GO
 

4.3 在各种处理中应用排序规则的示例

--1. 为数据库指定排序规则
CREATE DATABASE db COLLATE Chinese_PRC_CI_AS
GO

ALTER DATABASE db COLLATE Chinese_PRC_BIN
GO

/*====================================*/

--2. 为表中的列指定排序规则
CREATE TABLE tb(
col1 varchar(10),
col2 varchar(10) COLLATE Chinese_PRC_CI_AS)
GO

ALTER TABLE tb ADD col3 varchar(10) COLLATE Chinese_PRC_BIN
GO

ALTER 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
 

4.4 其他

• 生成GB2312汉字表

SELECT TOP 71 ID=IDENTITY(int,176,1) INTO #a FROM syscolumns
SELECT TOP 94 ID=IDENTITY(int,161,1) INTO #b FROM syscolumns
SELECT ch=CAST(CAST(a.ID as binary(1))+CAST(b.ID as binary(1)) as char(2)) COLLATE Chinese_PRC_CS_AS_KS_WS
FROM #a a,#b b
WHERE NOT(a.ID=215 AND b.ID BETWEEN 250 AND 254)
ORDER BY ch
DROP TABLE #a,#b
 

• 生成GBK汉字表

--GBK/2: GB2312 汉字，0xB0A1 - 0xF7FE (不包含 0xD7FA - 0xD7FE)
SELECT TOP 72 id=identity(int,176,1) INTO #a1 FROM SYSCOLUMNS
SELECT TOP 94 id=identity(int,161,1) INTO #a2 FROM SYSCOLUMNS
SELECT Code=CAST(a.id as binary(1))+CAST(b.id as binary(1)),
    [Char]=CAST(CAST(a.id as binary(1))+CAST(b.id as binary(1)) as char(2))
INTO #1
FROM #a1 a,#a2 b
WHERE NOT (a.id=215 AND b.id>=250)
DROP TABLE #a1,#a2

--GBK/3: 扩充汉字，0x8140 - 0xA0FE (不包含xx7F)
SELECT TOP 32 id=identity(int,129,1) INTO #b1 FROM SYSCOLUMNS
SELECT TOP 191 id=identity(int,64,1) INTO #b2 FROM SYSCOLUMNS
DELETE FROM #b2 WHERE id=127
SELECT Code=CAST(a.id as binary(1))+CAST(b.id as binary(1)),
    [Char]=CAST(CAST(a.id as binary(1))+CAST(b.id as binary(1)) as char(2))
INTO #2
FROM #b1 a,#b2 b
DROP TABLE #b1,#b2

--GBK/4: 扩充汉字，0xAA40 - 0xFEA0 (不包含xx7F)
SELECT TOP 85 id=identity(int,170,1) INTO #c1 FROM SYSCOLUMNS
SELECT TOP 97 id=identity(int,64,1) INTO #c2 FROM SYSCOLUMNS
DELETE FROM #c2 WHERE id=127
SELECT Code=CAST(a.id as binary(1))+CAST(b.id as binary(1)),
    [Char]=CAST(CAST(a.id as binary(1))+CAST(b.id as binary(1)) as char(2))
INTO #3
FROM #c1 a,#c2 b
DROP TABLE #c1,#c2

--合并
SELECT *,[Unicode]=UNICODE([char]) INTO # FROM #1
UNION ALL
SELECT *,[Unicode]=UNICODE([char]) FROM #2
UNION ALL
SELECT *,[Unicode]=UNICODE([char]) FROM #3

--编码不连续的
SELECT *,(SELECT MIN([Unicode]) FROM # WHERE [Unicode]>A.[Unicode])
FROM # A
WHERE NOT EXISTS(
    SELECT * FROM # WHERE [Unicode]=A.[Unicode]+1)
ORDER BY [Unicode]

-->40869的汉字 （从 19968 - 40869 的 20902 个汉字 UNICODE 编码是连续的)
SELECT * FROM # WHERE [Unicode]>40869
ORDER BY [Unicode]

DROP TABLE #1,#2,#3,#

• 自动获取汉字笔画

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fun_getbh]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fun_getbh]
GO

/*--得到汉字笔画

    得到指定汉字的笔画数
    引自:http://expert.csdn.net/Expert/topic/3051/3051568.xml?temp=.221142

--作者: J9988 --*/
create function fun_getbh(@char nchar(2))
returns int
as
begin
return(
    case when unicode(@char) between 19968 and 40869 then(
        select top 1 id from(
            select id=1,ch=N'亅'      union all select 2,N'阝' 
            union all select 3,N'马'  union all select 4,N'风' 
            union all select 5,N'龙'  union all select 6,N'齐' 
            union all select 7,N'龟'  union all select 8,N'齿' 
            union all select 9,N'鸩'  union all select 10,N'龀' 
            union all select 11,N'龛' union all select 12,N'龂' 
            union all select 13,N'龆' union all select 14,N'龈' 
            union all select 15,N'龊' union all select 16,N'龍' 
            union all select 17,N'龠' union all select 18,N'龎' 
            union all select 19,N'龐' union all select 20,N'龑' 
            union all select 21,N'龡' union all select 22,N'龢' 
            union all select 23,N'龝' union all select 24,N'齹' 
            union all select 25,N'龣' union all select 26,N'龥' 
            union all select 27,N'齈' union all select 28,N'龞' 
            union all select 29,N'麷' union all select 30,N'鸞' 
            union all select 31,N'麣' union all select 32,N'龖' 
            union all select 33,N'龗' union all select 35,N'齾' 
            union all select 36,N'齉' union all select 39,N'靐' 
            union all select 64,N'龘' 
        )a where ch>=@char collate Chinese_PRC_Stroke_CS_AS_KS_WS
        order by id ASC) else 0 end)
end
go

--函数调用实例：
select dbo.fun_getbh('华'),dbo.fun_getbh('華')