汉字转拼音的存储过程

[iamlain]

create or replace function GetPinYin(Keyword in varchar2) return varchar2 as
i number;
j number;
PinYin varchar2(500);
Temp varchar2(10);
TempStr varchar2(2);
--oracle处理中英文混合字符串的bug，因此函数中采用在任何字符串前添加一个★号来强制oracle把此字符串当作双字节来处理
begin
i := 1;
j := Length('★' || Keyword);
PinYin := '';
while i <= j LOOP
tempStr := substr('★' || KeyWord, i, 1);
select hzpy --如果换成substr(HZPY, 1, 1)则是取首字母，嘿嘿
INTO Temp
from t_Pinyin
Where Num =
(select max(num) from t_Pinyin Where Num <= ASCII(TempStr));
Temp := Replace(Temp, '*', TempStr);
Temp := Replace(Temp, '★', '');
PinYin := PinYin || Temp;
i := i + 1;
End loop;
return(PinYin);

end GetPinYin;



这个是FOR ORACLE的版本
有没有高手能帮忙重写成MS-SQL 版本啊 多谢了

[iamlain]

CREATE function dbo.GetPinYin (@Keyword nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @i INT,
@j INT
declare @PinYin nvarchar(4000),
@temp nvarchar(4000),
@tempStr nvarchar(4000)
set @i =1
set @j = len( @Keyword)
set @PinYin = ''
while (@i<=@j)
begin
set @tempStr = substring(@Keyword,@i,1)
declare c1 cursor for select hzpy from t_Pinyin where num=(select max(num) from t_Pinyin Where Num <= ASCII(@tempStr))
open c1
FETCH C1 INTO @temp
set @Temp = replace(@temp,'*',@tempStr)
set @PinYin = @PinYin + @temp
set @i = @i +1
close c1
deallocate c1
end
return @PinYin
end

[wk_1978]

收藏

[jixiaojie]

up

[iamlain]

--取汉字拼音的存储过程 原著Magic 修改 hey 2006.3.22

CREATE TABLE T_PINYIN (
　　 HZPY VARCHAR(10),
　　 NUM INT
　　)

INSERT INTO T_PINYIN(HZPY,NUM) VALUES('★','41455')

INSERT INTO T_PINYIN(HZPY,NUM) VALUES('*','255')

INSERT INTO T_PINYIN(HZPY,NUM) VALUES('*','1')

INSERT INTO T_PINYIN(HZPY,NUM) VALUES('a','45217')

INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ai','45219')

[ojuju10]

楼上的存储过程不对！！！

[laowang2]

up