1、按首字母排序
在oracle9i中新增了按照拼音、部首、笔画排序功能。设置NLS_SORT值
SCHINESE_RADICAL_M 按照部首(第一顺序)、笔划(第二顺序)排序
SCHINESE_STROKE_M 按照笔划(第一顺序)、部首(第二顺序)排序
SCHINESE_PINYIN_M 按照拼音排序
oracle9i中新增了按照拼音、部首、笔画排序功能
用法示例:
sql代码 拼音 SELECT*FROMTEAMORDERBYNLSSORT(排序字段,'NLS_SORT=SCHINESE_PINYIN_M') 笔划 SELECT*FROMTEAMORDERBYNLSSORT(排序字段,'NLS_SORT=SCHINESE_STROKE_M') 部首 SELECT*FROMTEAMORDERBYNLSSORT(排序字段,'NLS_SORT=SCHINESE_RADICAL_M')
2 按首字母拼音查询
首先我们要创建一个可以查询汉字首字母的函数,函数代码如下: CREATEORREPLACEFUNCTIONF_PINYIN(P_NAMEINVARCHAR2)RETURNVARCHAR2AS
V_COMPAREVARCHAR2(100);
V_RETURNVARCHAR2(4000);
FUNCTIONF_NLSSORT(P_WORDINVARCHAR2)RETURNVARCHAR2AS
BEGIN
returnNLSSORT(P_WORD,'NLS_SORT=SCHINESE_PINYIN_M');
END;
BEGIN
FORIIN1..LENGTH(P_NAME)LOOP
V_COMPARE:=F_NLSSORT(SUBSTR(P_NAME,I,1));
IFV_COMPARE>=F_NLSSORT('吖')ANDV_COMPARE<=F_NLSSORT('驁')THEN
V_RETURN:=V_RETURN||'A';
ELSIFV_COMPARE>=F_NLSSORT('八')ANDV_COMPARE<=F_NLSSORT('簿')THEN
V_RETURN:=V_RETURN||'B';
ELSIFV_COMPARE>=F_NLSSORT('嚓')ANDV_COMPARE<=F_NLSSORT('錯')THEN
V_RETURN:=V_RETURN||'C';
ELSIFV_COMPARE>=F_NLSSORT('咑')ANDV_COMPARE<=F_NLSSORT('鵽')THEN
V_RETURN:=V_RETURN||'D';
ELSIFV_COMPARE>=F_NLSSORT('妸')ANDV_COMPARE<=F_NLSSORT('樲')THEN
V_RETURN:=V_RETURN||'E';
ELSIFV_COMPARE>=F_NLSSORT('发')ANDV_COMPARE<=F_NLSSORT('猤')THEN
V_RETURN:=V_RETURN||'F';
ELSIFV_COMPARE>=F_NLSSORT('旮')ANDV_COMPARE<=F_NLSSORT('腂')THEN
V_RETURN:=V_RETURN||'G';
ELSIFV_COMPARE>=F_NLSSORT('妎')ANDV_COMPARE<=F_NLSSORT('夻')THEN
V_RETURN:=V_RETURN||'H';
ELSIFV_COMPARE>=F_NLSSORT('丌')ANDV_COMPARE<=F_NLSSORT('攈')THEN
V_RETURN:=V_RETURN||'J';
ELSIFV_COMPARE>=F_NLSSORT('咔')ANDV_COMPARE<=F_NLSSORT('穒')THEN
V_RETURN:=V_RETURN||'K';
ELSIFV_COMPARE>=F_NLSSORT('垃')ANDV_COMPARE<=F_NLSSORT('擽')THEN
V_RETURN:=V_RETURN||'L';
ELSIFV_COMPARE>=F_NLSSORT('嘸')ANDV_COMPARE<=F_NLSSORT('椧')THEN
V_RETURN:=V_RETURN||'M';
ELSIFV_COMPARE>=F_NLSSORT('拏')ANDV_COMPARE<=F_NLSSORT('瘧')THEN
V_RETURN:=V_RETURN||'N';
ELSIFV_COMPARE>=F_NLSSORT('筽')ANDV_COMPARE<=F_NLSSORT('漚')THEN
V_RETURN:=V_RETURN||'O';
ELSIFV_COMPARE>=F_NLSSORT('妑')ANDV_COMPARE<=F_NLSSORT('曝')THEN
V_RETURN:=V_RETURN||'P';
ELSIFV_COMPARE>=F_NLSSORT('七')ANDV_COMPARE<=F_NLSSORT('裠')THEN
V_RETURN:=V_RETURN||'Q';
ELSIFV_COMPARE>=F_NLSSORT('亽')ANDV_COMPARE<=F_NLSSORT('鶸')THEN
V_RETURN:=V_RETURN||'R';
ELSIFV_COMPARE>=F_NLSSORT('仨')ANDV_COMPARE<=F_NLSSORT('蜶')THEN
V_RETURN:=V_RETURN||'S';
ELSIFV_COMPARE>=F_NLSSORT('侤')ANDV_COMPARE<=F_NLSSORT('籜')THEN
V_RETURN:=V_RETURN||'T';
ELSIFV_COMPARE>=F_NLSSORT('屲')ANDV_COMPARE<=F_NLSSORT('鶩')THEN
V_RETURN:=V_RETURN||'W';
ELSIFV_COMPARE>=F_NLSSORT('夕')ANDV_COMPARE<=F_NLSSORT('鑂')THEN
V_RETURN:=V_RETURN||'X';
ELSIFV_COMPARE>=F_NLSSORT('丫')ANDV_COMPARE<=F_NLSSORT('韻')THEN
V_RETURN:=V_RETURN||'Y';
ELSIFV_COMPARE>=F_NLSSORT('帀')ANDV_COMPARE<=F_NLSSORT('咗')THEN
V_RETURN:=V_RETURN||'Z';
ENDIF;
ENDLOOP;
RETURNV_RETURN;
END;