create table #t(id int,name varchar(20)) insert #t select 1,'中' union all select 2,'国' union all select 3,'人' union all select 4,'阿'
select * from #t order by name collate Chinese_PRC_CS_AS_KS_WS drop table #t /*结果: id name ----------- -------------------- 4 阿 2 国 3 人 1 中 */
例2:让表NAME列的内容按姓氏笔划排序:
create table #t(id int,name varchar(20))
insert #t select 1,'三' union all select 2,'乙' union all select 3,'二' union all select 4,'一' union all select 5,'十' select * from #t order by name collate Chinese_PRC_Stroke_CS_AS_KS_WS drop table #t /*结果: id name ----------- -------------------- 4 一 2 乙 3 二 5 十 1 三 */
建汉字笔划表(TAB_HZBH): create table tab_hzbh(id int identity,cnword nchar(1)) --先插入前33个汉字 insert tab_hzbh select top 33 A.cnword from #t1 A left join #t1 B on A.id=B.id-1 and A.codeB.code where B.code is null order by A.id --再加最后四个汉字 set identity_insert tab_hzbh on go insert tab_hzbh(id,cnword) select 35,N'齾' union all select 36,N'齉' union all select 39,N'靐' union all select 64,N'龘' go set identity_insert tab_hzbh off go
到此为止,我们可以得到结果了,比如我们想得到汉字“国”的笔划:
declare @a nchar(1) set @a='国' select top 1 id from tab_hzbh where cnword>=@a collate Chinese_PRC_Stroke_CS_AS_KS_WS order by id
create function fun_getbh(@str nvarchar(4000)) returns int as begin declare @word nchar(1),@n int set @n=0 while len(@str)>0 begin set @word=left(@str,1) --如果非汉字,笔划当0计 set @n=@n+(case when unicode(@word) between 19968 and 19968+20901 then (select top 1 id from ( select 1 as id,N'亅' as word 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'龘' ) T where word>=@word collate Chinese_PRC_Stroke_CS_AS_KS_WS order by id ASC) else 0 end) set @str=right(@str,len(@str)-1) end return @n end
create function fun_getPY(@str nvarchar(4000)) returns nvarchar(4000) as begin declare @word nchar(1),@PY nvarchar(4000) set @PY='' while len(@str)>0 begin set @word=left(@str,1) --如果非汉字字符,返回原字符 set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901 then (select top 1 PY from ( select 'A' as PY,N'驁' as word union all select 'B',N'簿' union all select 'C',N'錯' union all select 'D',N'鵽' union all select 'E',N'樲' union all select 'F',N'鰒' union all select 'G',N'腂' union all select 'H',N'夻' union all select 'J',N'攈' union all select 'K',N'穒' union all select 'L',N'鱳' union all select 'M',N'旀' union all select 'N',N'桛' union all select 'O',N'漚' union all select 'P',N'曝' union all select 'Q',N'囕' union all select 'R',N'鶸' union all select 'S',N'蜶' union all select 'T',N'籜' union all select 'W',N'鶩' union all select 'X',N'鑂' union all select 'Y',N'韻' union all select 'Z',N'咗' ) T where word>=@word collate Chinese_PRC_CS_AS_KS_WS order by PY ASC) else @word end) set @str=right(@str,len(@str)-1) end return @PY end