专卖9会员资料导入后如何按名字生成拼音助记码

–创建取拼音函数
create function fGetPy(@Str varchar(500)=”)
returns varchar(500)
as

begin–函数实现开始
declare @strlen int,@return varchar(500),@ii int
declare @n int,@c char(1),@chn nchar(1)
select @strlen=len(@str),@return=”,@ii=0
set @ii=0
while @ii<@strlen
begin
  select @ii=@ii+1,@n=63,@chn=substring(@str,@ii,1)
if @chn>’z’
   select @n = @n +1,@c =   
    case chn
     when @chn then char(@n)
     else @c
     end
   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 ‘丌’   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 ‘屲’    union all select ‘屲’   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
else
 set @c=@chn
set @return=@return+@c
end
return(@return)
end
go—-2.对会员卡名字去首字母
use isszmv9
update t_rm_vip_info set vip_rem=replace(dbo.fgetpy(vip_name),’ ‘,”) where vip_rem=” or vip_rem is null–3.删除拼音函数
drop function fgetpy