sql server根据汉字生成拼音码的函数f_getpym()

  根据汉字生成拼音码的函数

-----------------------------语法-----------------------------

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)