mssql sqlserver 获取指定汉字的笔画数的方法分享

例:建立汉字笔画数sql函数 

X






6 rows selected.

可见,越往后的汉字的笔画数越多。

8.Oracle官方文档中关于NSLSORT函数的描述参考
http://download.oracle.com/docs/cd/E11882\_01/server.112/e10592/functions111.htm\#SQLRF00678

9.小结
NSLSORT函数在国际化支持上提供了一个非常好的排序解决方案。在具体应用环境下有其重要的意义。善用之。

Good luck.

 

如果数据库字符集选用的是ZH16GBK,那么使用order
by默认是按照汉字的“二进制编码”顺序进行排序的。有方法改变这个默认规则么?
答案是肯定的,Oracle针对简体中文提供三种排序方法,主要是围绕“拼音”、“部首”和“笔画数”展开的。
通过实验,给大家展示一下NLSSORT在改变简体汉字排序规则方面的魅力。

下文讲述计算汉字笔画数的sql函数分享,如下所示:

X






6 rows selected.

单人旁的汉字在前,提手旁汉字在后;单人旁的三个汉字进一步又是按照笔画数多少进行的排序。

7.使用nlssort强制按照“笔画数”(第一顺序)和“部首”(第二顺序)排序的方法
sec@secooler> select * from t order by
nlssort(x,’NLS_SORT=SCHINESE_STROKE_M’);


USERENV(‘LANGUAGE’)

AMERICAN_AMERICA.ZHS16GBK

4.在此环境下,查看默认的汉字排序规则
sec@secooler> select * from t order by x;

转自:http://www.maomao365.com/?p=6421

X






6 rows selected.

3.确认数据库版本和数据库字符集
sec@secooler> select * from v$version;

create function [dbo].[fn_hanZiBiHuaShu](@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

--测试获取汉字笔画数的sql函数 
select dbo.[fn_hanZiBiHuaShu]('猫')

drop function dbo.[fn_hanZiBiHuaShu]
go

4)TCHINESE_RADICAL_M
Traditional Chinese sort based on radical as primary order and number of
strokes order as secondary order
注释:繁体中文按照第一顺序是“部首”第二顺序是“笔画数”进行排序;

摘要:

X






6 rows selected.

6.使用nlssort强制按照“部首”(第一顺序)和“笔画数”(第二顺序)排序的方法
sec@secooler> select * from t order by
nlssort(x,’NLS_SORT=SCHINESE_RADICAL_M’);

BANNER

Oracle Database 11g Enterprise
Edition Release 11.2.0.1.0 – 64bit Production
PL/SQL Release 11.2.0.1.0 – Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 – Production
NLSRTL Version 11.2.0.1.0 – Production

sec@secooler> select userenv(‘language’) from dual;

1.在Oracle的官方文档中关于排序有如下描述
“Linguistic Sorts”
http://download.oracle.com/docs/cd/B19306\_01/server.102/b14225/applocaledata.htm\#sthref2000
其中表“Table A-15 Multilingual LInguistic
Sorts”中我们关注一下有关中文排序的内容(前三条与简体中文排序有关,后两条与繁体中文排序有关):
1)SCHINESE_RADICAL_M
Simplified Chinese sort based on radical as primary order and number of
strokes order as secondary order
注释:简体中文按照第一顺序是“部首”第二顺序是“笔画数”进行排序;

2)SCHINESE_STROKE_M
Simplified Chinese sort uses number of strokes as primary order and
radical as secondary order
注释:简体中文按照第一顺序“笔画数”是第二顺序是“部首”进行排序;

2.创建实验表T,并初始化六条记录
sec@secooler> create table t (x varchar2(10));
sec@secooler> insert into t values (‘侯’);
sec@secooler> insert into t values (‘你’);
sec@secooler> insert into t values (‘做’);
sec@secooler> insert into t values (‘拉’);
sec@secooler> insert into t values (‘推’);
sec@secooler> insert into t values (‘拆’);
sec@secooler> commit;
sec@secooler> select * from t;

X






6 rows selected.

可见,此时的默认汉字排序规则是“拼音”。

5.使用nlssort强制按照拼音排序的方法
sec@secooler> select * from t order by
nlssort(x,’NLS_SORT=SCHINESE_PINYIN_M’);

5)TCHINESE_STROKE_M
Traditional Chinese sort uses number of strokes as primary order and
radical as secondary order. It supports supplementary characters.
注释:繁体中文按照第一顺序“笔画数”是第二顺序是“部首”进行排序;

3)SCHINESE_PINYIN_M
Simplified Chinese PinYin sorting order
注释:简体中文按照“拼音”进行排序;