SQL 拼音码

--拼音码
create function [dbo].[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 '丌' --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 
else set @c=@chn
if Isnumeric(@chn)=1 
   set @return=@return+@chn
else
   set @return=@return+@c 
end 
return(@return) 
end

SQL 查询分析器直接刷新表中指定的字段为拼音码

/*查询一下，看下效果*/
select 客户编号,dbo.fGetPy(客户名称) from 客户表

update 客户表
set 拼音码 = dbo.fGetPy(客户名称)