根据汉字生成拼音码的函数
-----------------------------语法-----------------------------
create function f_getpym(@srcName nvarchar(1000)='')
returns varchar(100)
begin
declare @returnValue varchar(1000)
select @srcName = rtrim(ltrim(@srcName))--去除左右空格
if len(@srcName) <= 0
begin
select @returnValue = null --如果需要转换的内容长度小于0，直接返回
end
select @returnValue = ''
declare @letter varchar(10)
declare @len int
select @letter = '',@len = 0
while(@len <= len(@srcName))
begin
select @letter = case
when substring(@srcName, @len, 1) between '阿' and '鏊' or substring(upper(@srcName), @len, 1) = 'A' then 'A'
when substring(@srcName, @len, 1) between '八' and '簿' or substring(@srcName, @len, 1) = '8' or substring(upper(@srcName), @len, 1) = 'B' then 'B'
when substring(@srcName, @len, 1) between '嚓' and '错' or substring(upper(@srcName), @len, 1) = 'C' then 'C'
when substring(@srcName, @len, 1) between '哒' and '跺' or substring(upper(@srcName), @len, 1) = 'D' then 'D'
when substring(@srcName, @len, 1) between '屙' and '贰' or substring(@srcName, @len, 1) = '2' or substring(upper(@srcName), @len, 1) = 'E' then 'E'
when substring(@srcName, @len, 1) between '发' and '馥' or substring(upper(@srcName), @len, 1) = 'F' then 'F'
when substring(@srcName, @len, 1) between '旮' and '过' or substring(upper(@srcName), @len, 1) = 'G' then 'G'
when substring(@srcName, @len, 1) between '铪' and '蠖' or substring(upper(@srcName), @len, 1) = 'H' then 'H'
when (substring(@srcName, @len, 1) between '丌' and '竣' or substring(@srcName, @len, 1) = '9' or substring(upper(@srcName), @len, 1) = 'J') and substring(@srcName, 1, 1) <> '她' then 'J'
when substring(@srcName, @len, 1) between '咔' and '廓' or substring(upper(@srcName), @len, 1) = 'K' then 'K'
when substring(@srcName, @len, 1) between '垃' and '雒' or substring(@srcName, @len, 1) in ('0', '6') or substring(upper(@srcName), @len, 1) = 'L' then 'L'
when substring(@srcName, @len, 1) between '妈' and '穆' or substring(upper(@srcName), @len, 1) = 'M' then 'M'
when substring(@srcName, @len, 1) between '拿' and '糯' or substring(upper(@srcName), @len, 1) = 'N' then 'N'
when substring(@srcName, @len, 1) between '噢' and '沤' or substring(upper(@srcName), @len, 1) = 'O' then 'O'
when substring(@srcName, @len, 1) between '趴' and '曝' or substring(upper(@srcName), @len, 1) = 'P' then 'P'
when substring(@srcName, @len, 1) between '七' and '群' or substring(@srcName, @len, 1) = '7' or substring(upper(@srcName), @len, 1) = 'Q' then 'Q'
when substring(@srcName, @len, 1) between '蚺' and '箬' or substring(upper(@srcName), @len, 1) = 'R' then 'R'
when substring(@srcName, @len, 1) between '仨' and '锁' or substring(@srcName, @len, 1) in ('3', '4') or substring(upper(@srcName), @len, 1) = 'S' then 'S'
when substring(@srcName, @len, 1) between '他' and '箨' or substring(upper(@srcName), @len, 1) = 'T' or substring(@srcName, 1, 1) = '她' then 'T'
when substring(@srcName, @len, 1) between '哇' and '鋈' or substring(@srcName, @len, 1) = '5' or substring(upper(@srcName), @len, 1) = 'W' then 'W'
when substring(@srcName, @len, 1) between '夕' and '蕈' or substring(upper(@srcName), @len, 1) = 'X' then 'X'
when substring(@srcName, @len, 1) between '丫' and '蕴' or substring(@srcName, @len, 1) = '1' or substring(upper(@srcName), @len, 1) = 'Y' then 'Y'
when substring(@srcName, @len, 1) between '匝' and '做' or substring(upper(@srcName), @len, 1) = 'Z' then 'Z'
when substring(upper(@srcName), @len, 1) = 'I' then 'I'
when substring(upper(@srcName), @len, 1) = 'U' then 'U'
when substring(upper(@srcName), @len, 1) = 'V' then 'V'
else ''
end
select @len = @len + 1
select @returnValue = @returnValue + @letter
end
if len(@returnValue) = 0
begin
select @returnValue = null
end
return @returnValue
end
-----------------------------调用-----------------------------
update wj_gys set gys_pym = dbo.f_getpym(gys_cname)