create function fun_getPY(str nvarchar) returns nvarchar as word nchar(1); PY nvarchar(4000); begin PY:=''; while length(str)>0 loop word:=substr(str,1,1) --如果非汉字字符,返回原字符 select (case when ascII(word) between 19968 and 19968+20901 then (select py from (select 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) where rownum<2) else word end) into PY from dual; str:=substr(str,2) ; end loop; return PY; end ; / 没有测试
调试通不过啊。可能oracle的机制和sql server太不一样吧。 等几天,没人回就结贴了
create or replace function fun_getPY(str nvarchar2) return nvarchar2 is PY nvarchar2(4000); f_str nvarchar2(4000); f_word nvarchar2(1); t_py nvarchar2(1); begin f_str := str; PY:=''; while length(f_str)>0 loop f_word :=substr(f_str,1,1) ; if ( (Ascii(f_word) >= 19968) and (Ascii(f_word) <= 19968+20901) ) then select py into t_py from ( select PY from ( select 'A' as PY, N'驁' as word from dual union all select 'B',N'簿' from dual union all select 'C',N'錯' from dual union all select 'D',N'鵽' from dual union all select 'E',N'樲' from dual union all select 'F',N'鰒' from dual union all select 'G',N'腂' from dual union all select 'H',N'夻' from dual union all select 'J',N'攈' from dual union all select 'K',N'穒' from dual union all select 'L',N'鱳' from dual union all select 'M',N'旀' from dual union all select 'N',N'桛' from dual union all select 'O',N'漚' from dual union all select 'P',N'曝' from dual union all select 'Q',N'囕' from dual union all select 'R',N'鶸' from dual union all select 'S',N'蜶' from dual union all select 'T',N'籜' from dual union all select 'W',N'鶩' from dual union all select 'X',N'鑂' from dual union all select 'Y',N'韻' from dual union all select 'Z',N'咗' from dual ) T where word >= f_word --collate Chinese_PRC_CS_AS_KS_WS order by PY ASC) where rownum<2; else t_py := f_word; end if; PY := PY||t_py; f_str := substr(f_str,2) ; end loop; return PY; end fun_getPY;已经调试通过但是oracle的排序机制跟sqlserver不一样。而且我在调试的过程中发现 Ascii('驁') = 62088 比你的19968+20901 要大好多啊, 我的oracle的字符集为 NLS_LANGE = ZHS16GBK ;NLS_SORT = BINARY 你可能要修改这些值让其在ORACLE下工作正常。
Oracle9i之前,中文是按照二进制编码进行排序的。在oracle9i中新增了按照拼音、部首、笔画排序功能。设置NLS_SORT值 SCHINESE_RADICAL_M 按照部首(第一顺序)、笔划(第二顺序)排序 SCHINESE_STROKE_M 按照笔划(第一顺序)、部首(第二顺序)排序 SCHINESE_PINYIN_M 按照拼音排序我们可以在环境变量中设置NLS_SORT(Windows客户端在注册表homeX里,例如加入nls_sort = SCHINESE_PINYIN_M),也可以使用alter session进行设置,还可以使用NLSSORT函数。SQL> l 1 select c1 ,ascii(c1) from a order by 2* NLSSORT(c1,'NLS_SORT = SCHINESE_STROKE_M') SQL> /C1 ASCII(C1) -------------------- ---------- 永 54208 诚 46031 贤 53197 啊 45217 程 46028 新 53442
to: zzw0598(口是心非) 我又翻了翻资料,周末没事就调试了一下, 这个在oracle下工作正常了 :) 哈海支持中英文混排。create or replace function fun_getPY(str nvarchar2) return nvarchar2 is PY varchar2(4000); f_str varchar2(4000); f_word varchar2(2); f_py varchar2(2); begin f_str := str; PY:=''; while length(f_str)>0 loop f_word :=substr(f_str,1,1) ; if ( (Ascii(f_word) >= 33088) and (Ascii(f_word) <= 64923) ) then select py into f_py from ( select rownum xh,py,word from ( select PY,word from ( select 'A' as PY, '吖' as word from dual union all select 'B','八' from dual union all select 'C','嚓' from dual union all select 'D','咑' from dual union all select 'E','妸' from dual union all select 'F','发' from dual union all select 'G','旮' from dual union all select 'H','铪' from dual union all select 'J','丌' from dual union all select 'K','咔' from dual union all select 'L','垃' from dual union all select 'M','嘸' from dual union all select 'N','拏' from dual union all select 'O','噢' from dual union all select 'P','妑' from dual union all select 'Q','七' from dual union all select 'R','亽' from dual union all select 'S','仨' from dual union all select 'T','侤' from dual union all select 'W','屲' from dual union all select 'X','夕' from dual union all select 'Y','丫' from dual union all select 'Z','帀' from dual union all select 'a',f_word from dual ) T order by NLSSORT(word,'NLS_SORT = SCHINESE_PINYIN_M') )m)where xh = (select xh from ( select rownum - 1 xh,py,word from ( select PY,word from ( select 'A' as PY, '吖' as word from dual union all select 'B','八' from dual union all select 'C','嚓' from dual union all select 'D','咑' from dual union all select 'E','妸' from dual union all select 'F','发' from dual union all select 'G','旮' from dual union all select 'H','铪' from dual union all select 'J','丌' from dual union all select 'K','咔' from dual union all select 'L','垃' from dual union all select 'M','嘸' from dual union all select 'N','那' from dual union all select 'O','噢' from dual union all select 'P','妑' from dual union all select 'Q','七' from dual union all select 'R','亽' from dual union all select 'S','仨' from dual union all select 'T','侤' from dual union all select 'W','屲' from dual union all select 'X','夕' from dual union all select 'Y','丫' from dual union all select 'Z','帀' from dual union all select 'a',f_word from dual ) T order by NLSSORT(word,'NLS_SORT = SCHINESE_PINYIN_M') ) )where word = f_word); else f_py := f_word; end if; PY := PY||f_py; f_str := substr(f_str,2) ; end loop; return PY; end fun_getPY;
returns nvarchar
as
word nchar(1);
PY nvarchar(4000);
begin
PY:='';
while length(str)>0 loop
word:=substr(str,1,1)
--如果非汉字字符,返回原字符
select (case when ascII(word) between 19968 and 19968+20901
then (select py from (select 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) where rownum<2) else word end) into PY from dual;
str:=substr(str,2) ;
end loop;
return PY;
end ;
/
没有测试
等几天,没人回就结贴了
return nvarchar2
is PY nvarchar2(4000);
f_str nvarchar2(4000);
f_word nvarchar2(1);
t_py nvarchar2(1);
begin
f_str := str;
PY:='';
while length(f_str)>0 loop
f_word :=substr(f_str,1,1) ; if ( (Ascii(f_word) >= 19968) and (Ascii(f_word) <= 19968+20901) ) then
select py into t_py from (
select PY from
(
select 'A' as PY, N'驁' as word from dual
union all select 'B',N'簿' from dual
union all select 'C',N'錯' from dual
union all select 'D',N'鵽' from dual
union all select 'E',N'樲' from dual
union all select 'F',N'鰒' from dual
union all select 'G',N'腂' from dual
union all select 'H',N'夻' from dual
union all select 'J',N'攈' from dual
union all select 'K',N'穒' from dual
union all select 'L',N'鱳' from dual
union all select 'M',N'旀' from dual
union all select 'N',N'桛' from dual
union all select 'O',N'漚' from dual
union all select 'P',N'曝' from dual
union all select 'Q',N'囕' from dual
union all select 'R',N'鶸' from dual
union all select 'S',N'蜶' from dual
union all select 'T',N'籜' from dual
union all select 'W',N'鶩' from dual
union all select 'X',N'鑂' from dual
union all select 'Y',N'韻' from dual
union all select 'Z',N'咗' from dual
) T
where word >= f_word --collate Chinese_PRC_CS_AS_KS_WS
order by PY ASC)
where rownum<2;
else
t_py := f_word;
end if;
PY := PY||t_py;
f_str := substr(f_str,2) ;
end loop;
return PY;
end fun_getPY;已经调试通过但是oracle的排序机制跟sqlserver不一样。而且我在调试的过程中发现
Ascii('驁') = 62088 比你的19968+20901 要大好多啊,
我的oracle的字符集为 NLS_LANGE = ZHS16GBK ;NLS_SORT = BINARY
你可能要修改这些值让其在ORACLE下工作正常。
SCHINESE_RADICAL_M 按照部首(第一顺序)、笔划(第二顺序)排序
SCHINESE_STROKE_M 按照笔划(第一顺序)、部首(第二顺序)排序
SCHINESE_PINYIN_M 按照拼音排序我们可以在环境变量中设置NLS_SORT(Windows客户端在注册表homeX里,例如加入nls_sort = SCHINESE_PINYIN_M),也可以使用alter session进行设置,还可以使用NLSSORT函数。SQL> l
1 select c1 ,ascii(c1) from a order by
2* NLSSORT(c1,'NLS_SORT = SCHINESE_STROKE_M')
SQL> /C1 ASCII(C1)
-------------------- ----------
永 54208
诚 46031
贤 53197
啊 45217
程 46028
新 53442
我又翻了翻资料,周末没事就调试了一下, 这个在oracle下工作正常了 :)
哈海支持中英文混排。create or replace function fun_getPY(str nvarchar2)
return nvarchar2
is PY varchar2(4000);
f_str varchar2(4000);
f_word varchar2(2);
f_py varchar2(2);
begin
f_str := str;
PY:='';
while length(f_str)>0 loop
f_word :=substr(f_str,1,1) ; if ( (Ascii(f_word) >= 33088) and (Ascii(f_word) <= 64923) ) then
select py into f_py from (
select rownum xh,py,word from (
select PY,word from
( select 'A' as PY, '吖' as word from dual
union all select 'B','八' from dual
union all select 'C','嚓' from dual
union all select 'D','咑' from dual
union all select 'E','妸' from dual
union all select 'F','发' from dual
union all select 'G','旮' from dual
union all select 'H','铪' from dual
union all select 'J','丌' from dual
union all select 'K','咔' from dual
union all select 'L','垃' from dual
union all select 'M','嘸' from dual
union all select 'N','拏' from dual
union all select 'O','噢' from dual
union all select 'P','妑' from dual
union all select 'Q','七' from dual
union all select 'R','亽' from dual
union all select 'S','仨' from dual
union all select 'T','侤' from dual
union all select 'W','屲' from dual
union all select 'X','夕' from dual
union all select 'Y','丫' from dual
union all select 'Z','帀' from dual
union all select 'a',f_word from dual
) T order by NLSSORT(word,'NLS_SORT = SCHINESE_PINYIN_M')
)m)where xh = (select xh from (
select rownum - 1 xh,py,word from (
select PY,word from
( select 'A' as PY, '吖' as word from dual
union all select 'B','八' from dual
union all select 'C','嚓' from dual
union all select 'D','咑' from dual
union all select 'E','妸' from dual
union all select 'F','发' from dual
union all select 'G','旮' from dual
union all select 'H','铪' from dual
union all select 'J','丌' from dual
union all select 'K','咔' from dual
union all select 'L','垃' from dual
union all select 'M','嘸' from dual
union all select 'N','那' from dual
union all select 'O','噢' from dual
union all select 'P','妑' from dual
union all select 'Q','七' from dual
union all select 'R','亽' from dual
union all select 'S','仨' from dual
union all select 'T','侤' from dual
union all select 'W','屲' from dual
union all select 'X','夕' from dual
union all select 'Y','丫' from dual
union all select 'Z','帀' from dual
union all select 'a',f_word from dual
) T order by NLSSORT(word,'NLS_SORT = SCHINESE_PINYIN_M')
) )where word = f_word);
else
f_py := f_word;
end if;
PY := PY||f_py;
f_str := substr(f_str,2) ;
end loop;
return PY;
end fun_getPY;