利用函数进行汉字首字母辨别，对字段设置需要的值

现在，我需要把一个表里面的“拼音首字母”的一个字段，根据“标题”内容的汉字的首字母来设置值，当“标题”的内容的汉字的首字母在a~d范围内，就设“拼音首字母”值为1097；当“标题”的内容的汉字的首字母在e~h范围内，就设“拼音首字母”值为1098；当“标题”的内容的汉字的首字母在i~l范围内，就设“拼音首字母”值为1099；当“标题”的内容的汉字的首字母在m~p范围内，就设“拼音首字母”值为1100；当“标题”的内容的汉字的首字母在q~t范围内，就设“拼音首字母”值为1101；当“标题”的内容的汉字的首字母在u~x范围内，就设“拼音首字母”值为1102；当“标题”的内容的汉字的首字母在y~z范围内，就设“拼音首字母”值为1103。

        在网上找了一个取汉字拼音的函数：

create function fun_getPY(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @word nchar(1),@PY nvarchar(4000)
set @PY=''
while len(@str)>0
begin
set @word=left(@str,1)
--如果非汉字字符，返回原字符
set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901
then (select top 1 PY from (
select 'A' as PY,N'驁' as word
union all select 'B',N'簿'
union all select 'C',N'錯'
union all select 'D',N'鵽'
union all select 'E',N'樲'
union all select 'F',N'鰒'
union all select 'G',N'腂'
union all select 'H',N'夻'
union all select 'J',N'攈'
union all select 'K',N'穒'
union all select 'L',N'鱳'
union all select 'M',N'旀'
union all select 'N',N'桛'
union all select 'O',N'漚'
union all select 'P',N'曝'
union all select 'Q',N'囕'
union all select 'R',N'鶸'
union all select 'S',N'蜶'
union all select 'T',N'籜'
union all select 'W',N'鶩'
union all select 'X',N'鑂'
union all select 'Y',N'韻'
union all select 'Z',N'咗'
) T
where word>=@word collate Chinese_PRC_CS_AS_KS_WS
order by PY ASC) else @word end)
set @str=right(@str,len(@str)-1)
end
return @PY
end

 

然后对表进行查询：

DECLARE @i int
DECLARE @pinying varchar(40)
set @i = 1000
while(select Dy_gameAutoId from dy_gameifo where Dy_gameAutoId = @i) <=1180
begin
    select @pinying = SUBSTRING(dbo.fun_getPY(Dy_gameGameName),1,1) from dy_gameifo where Dy_gameAutoId = @i
    if LOWER(@pinying)>='a' and LOWER(@pinying) <='d'
      update dy_gameifo set Dy_gameFistWord_csClassId = 1097 where Dy_gameFistWord_csClassId is null and Dy_gameAutoId = @i

    if LOWER(@pinying)>='e' and LOWER(@pinying) <='h'
      update dy_gameifo set Dy_gameFistWord_csClassId = 1098 where Dy_gameFistWord_csClassId is null and Dy_gameAutoId = @i

    if LOWER(@pinying)>='i' and LOWER(@pinying) <='l'
      update dy_gameifo set Dy_gameFistWord_csClassId = 1099 where Dy_gameFistWord_csClassId is null and Dy_gameAutoId = @i

    if LOWER(@pinying)>='m' and LOWER(@pinying) <='p'
      update dy_gameifo set Dy_gameFistWord_csClassId = 1100 where Dy_gameFistWord_csClassId is null and Dy_gameAutoId = @i

    if LOWER(@pinying)>='q' and LOWER(@pinying) <='t'
      update dy_gameifo set Dy_gameFistWord_csClassId = 1101 where Dy_gameFistWord_csClassId is null and Dy_gameAutoId = @i

    if LOWER(@pinying)>='u' and LOWER(@pinying) <='x'
      update dy_gameifo set Dy_gameFistWord_csClassId = 1102 where Dy_gameFistWord_csClassId is null and Dy_gameAutoId = @i
   
    if LOWER(@pinying)>='y' and LOWER(@pinying) <='z'
      update dy_gameifo set Dy_gameFistWord_csClassId = 1103 where Dy_gameFistWord_csClassId is null and Dy_gameAutoId = @i
    set @i=@i+1
end

 

优化一下：

DECLARE @i int
DECLARE @pinying varchar(40)
set @i = 1000
while @i <=1180
begin
    select @pinying = SUBSTRING(dbo.fun_getPY(Dy_gameGameName),1,1) from dy_gameifo where Dy_gameAutoId = @i
    update dy_gameifo
    set Dy_gameFistWord_csClassId = (case when (LOWER(@pinying)>='a' and LOWER(@pinying) <='d') then 1097
                                    when (LOWER(@pinying)>='e' and LOWER(@pinying) <='h') then 1098
                                    when (LOWER(@pinying)>='i' and LOWER(@pinying) <='l') then 1099
                                    when (LOWER(@pinying)>='m' and LOWER(@pinying) <='p') then 1100
                                    when (LOWER(@pinying)>='q' and LOWER(@pinying) <='t') then 1101
                                    when (LOWER(@pinying)>='u' and LOWER(@pinying) <='x') then 1102
                                    when (LOWER(@pinying)>='y' and LOWER(@pinying) <='z') then 1103
                                    else Dy_gameFistWord_csClassId end)
    where Dy_gameFistWord_csClassId is null and Dy_gameAutoId = @i
   
    set @i=@i+1
end