sql server如何獲取中文首字母函數(shù)

更新時(shí)間：2025年06月09日 11:06:35 作者：軟泡芙

這篇文章主要介紹了sql server如何獲取中文首字母函數(shù)的問題,具有很好的參考價(jià)值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

sql server獲取中文首字母函數(shù)

java的同學(xué)可以了解下pinyin4j，js可以了解ChinesePY.js

獲取拼音的意義多在于需對中文進(jìn)行排序，例如：通訊錄。

獲取漢字首字母的解決方案有

利用CJK統(tǒng)一表意符
利用GBK
利用Unicode編碼

利用Unicode編碼

第二個(gè)方法不太好用有的字獲取不出來，這個(gè)比較頂

ALTER function [dbo].[GetPY](@str varchar(500))

returns varchar(500)

as
begin
declare @word nchar(1),@PY nvarchar(4000)
set @PY=''
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

利用GBK

把@length=1改成@length=1len(@str) ，效果從輸入首字的首字母變成輸出全部字的首字母

create function [dbo].[GetPY](@str varchar(500)) 

returns varchar(500) 

as 

begin 

   declare @cyc int,@length int,@str1 varchar(100),@charcate varbinary(20) 

   set @cyc=1--從第幾個(gè)字開始取 

   set @length=1--len(@str)--輸入漢字的長度 只需要返回首字母

   set @str1=''--用于存放返回值 

   while @cyc<=@length 

       begin   

          select @charcate=cast(substring(@str,@cyc,1) as varbinary)--每次取出一個(gè)字并將其轉(zhuǎn)變成二進(jìn)制,便于與GBK編碼表進(jìn)行比較 
if @str LIKE '[a-z]%' --英文開頭直接返回
	return @str 
if @charcate>=0XB0A1 and @charcate<=0XB0C4 

         set @str1=@str1+'a'--說明此漢字的首字母為A,以下同上 

    else if @charcate>=0XB0C5 and @charcate<=0XB2C0 

      set @str1=@str1+'b' 

else if @charcate>=0XB2C1 and @charcate<=0XB4ED 

      set @str1=@str1+'c' 

else if @charcate>=0XB4EE and @charcate<=0XB6E9 

      set @str1=@str1+'d' 

else if @charcate>=0XB6EA and @charcate<=0XB7A1 

                       set @str1=@str1+'e' 

else if @charcate>=0XB7A2 and @charcate<=0XB8C0 

             set @str1=@str1+'f' 

else if @charcate>=0XB8C1 and @charcate<=0XB9FD 

                       set @str1=@str1+'g' 

else if @charcate>=0XB9FE and @charcate<=0XBBF6 

       set @str1=@str1+'h' 

else if @charcate>=0XBBF7 and @charcate<=0XBFA5 

       set @str1=@str1+'i' 

else if @charcate>=0XBFA6 and @charcate<=0XC0AB 

       set @str1=@str1+'j' 

else if @charcate>=0XC0AC and @charcate<=0XC2E7 

       set @str1=@str1+'k' 

else if @charcate>=0XC2E8 and @charcate<=0XC4C2 

       set @str1=@str1+'l' 
else if @charcate>=0XC2E8 and @charcate<=0XC4C2 

       set @str1=@str1+'m' 
else if @charcate>=0XC4C3 and @charcate<=0XC5B5 

       set @str1=@str1+'n' 

   else if @charcate>=0XC5B6 and @charcate<=0XC5BD 

       set @str1=@str1+'o' 

else if @charcate>=0XC5BE and @charcate<=0XC6D9 

       set @str1=@str1+'p' 

else if @charcate>=0XC6DA and @charcate<=0XC8BA 

       set @str1=@str1+'q' 

else if @charcate>=0XC8BB and @charcate<=0XC8F5 

                   set @str1=@str1+'r' 

else if @charcate>=0XC8F6 and @charcate<=0XCBF9 

       set @str1=@str1+'s' 

else if @charcate>=0XCBFA and @charcate<=0XCDD9 

      set @str1=@str1+'t' 

else if @charcate>=0XCDDA and @charcate<=0XCEF3 

        set @str1=@str1+'w' 

else if @charcate>=0XCEF4 and @charcate<=0XD1B8 

        set @str1=@str1+'x' 

else if @charcate>=0XD1B9 and @charcate<=0XD4D0 

       set @str1=@str1+'y' 

else if @charcate>=0XD4D1 and @charcate<=0XD7F9 

       set @str1=@str1+'z' 

       set @cyc=@cyc+1--取出輸入漢字的下一個(gè)字 

end 

return @str1--返回輸入漢字的首字母 

       end