【问】食通天6.5批量生成拼音简码
【适用版本】食通天6.5	【模块】基础业务	【点击数】56
【知识编号】92017092600006
【问题描述】食通天6.5批量生成拼音简码。

【答】 请备份客户数据库后，参考以下语句分步执行处理。—–首先
use issrestv6
go
CREATE        function fGetPy(@str varchar(500)=”)
returns varchar(500)
as
begin
    declare @strlen int,@return varchar(500),@ii int
    declare @c nchar(1),@chn nchar(1)
    select @strlen=len(@str),@return=”,@ii=0
    set @ii=0
    while @ii<@strlen
    begin
        select @ii=@ii+1,@chn=substring(@str,@ii,1)
      if @chn>=’吖’
        select @c = char(count(*)+63)  from (
              select top 27 * from (
                    select chn = ‘吖’
                    union all select ‘八’
                    union all select ‘嚓’
                    union all select ‘咑’
                    union all select ‘妸’
                    union all select ‘发’
                    union all select ‘旮’
                    union all select ‘铪’
                    union all select ‘丌’        –because have no ‘i’
                    union all select ‘丌’
                    union all select ‘咔’
                    union all select ‘垃’
                    union all select ‘嘸’
                    union all select ‘拏’
                    union all select ‘噢’
                    union all select ‘妑’
                    union all select ‘七’
                    union all select ‘呥’
                    union all select ‘仨’
                    union all select ‘他’
                    union all select ‘屲’        –no ‘u’
                    union all select ‘屲’        –no ‘v’
                    union all select ‘屲’
                    union all select ‘夕’
                    union all select ‘丫’
                    union all select ‘帀’
                    union all select @chn
                                          )  as a
                            order by chn COLLATE Chinese_PRC_CI_AS ) as b
              where   b.chn <=@chn
        else set @c=@chn
              set @return=@return+@c
  end
    return(@return)
end
–其次
select dbo.fGetPy(‘东莞市’) as 东莞市,dbo.fGetPy(‘ab中c国人’) as 中国人
–最后
select * from cybr_bt_dish
update cybr_bt_dish set vch_spell=dbo.fGetPy(vch_dishname)
(如果执行时提示转换失败是因为拼音码太长超出15的长度可将表长度改为30执行后再改回15）