SQL Server 欄位提取拼音首字母

目前工作中遇到一個情況，需要將SQL Server中的一個欄位提取拼音的首字母，欄位由漢字、英文、數字以及“-”構成，百度了一堆，找到如下方法，記錄一下，以備後用！

　　首先建立一個函數

--產生拼音首碼    CREATE  function  fn_GetPy(@str  nvarchar(4000))    returns  nvarchar(4000)    --WITH  ENCRYPTION    as    begin    declare  @intLen int    declare  @strRet nvarchar(4000)    declare  @temp  nvarchar(100)    set  @intLen  =  len(@str)    set  @strRet  =  ''    while  @intLen  >  0    begin    set  @temp  =  ''    select  @temp  =  case      when  substring(@str,@intLen,1)  >=  '帀'  then  'Z'    when  substring(@str,@intLen,1)  >=  '丫'  then  'Y'    when  substring(@str,@intLen,1)  >=  '夕'  then  'X'    when  substring(@str,@intLen,1)  >=  '屲'  then  'W'    when  substring(@str,@intLen,1)  >=  '他'  then  'T'    when  substring(@str,@intLen,1)  >=  '仨'  then  'S'    when  substring(@str,@intLen,1)  >=  '呥'  then  'R'    when  substring(@str,@intLen,1)  >=  '七'  then  'Q'    when  substring(@str,@intLen,1)  >=  '妑'  then  'P'    when  substring(@str,@intLen,1)  >=  '噢'  then  'O'    when  substring(@str,@intLen,1)  >=  '拏'  then  'N'    when  substring(@str,@intLen,1)  >=  '嘸'  then  'M'    when  substring(@str,@intLen,1)  >=  '垃'  then  'L'    when  substring(@str,@intLen,1)  >=  '哢'  then  'K'    when  substring(@str,@intLen,1)  >=  '丌'  then    'J'    when  substring(@str,@intLen,1)  >=  '鉿'  then  'H'    when  substring(@str,@intLen,1)  >=  '旮'  then  'G'    when  substring(@str,@intLen,1)  >=  '發'  then  'F'    when  substring(@str,@intLen,1)  >=  '妸'  then  'E'    when  substring(@str,@intLen,1)  >=  '咑'  then  'D'    when  substring(@str,@intLen,1)  >=  '嚓'  then  'C'    when  substring(@str,@intLen,1)  >=  '八'  then  'B'    when  substring(@str,@intLen,1)  >=  '吖'  then  'A'    else  rtrim(ltrim(substring(@str,@intLen,1)))    end    --對於漢字特殊字元，不產生拼音碼    if  (ascii(@temp)>127)  set  @temp  =  ''    --對於英文中小括弧，不產生拼音碼    if  @temp  =  '('  or  @temp  =  ')'  set  @temp  =  ''    select  @strRet  =  @temp  +  @strRet    set  @intLen  =  @intLen  -  1    end    return  lower(@strRet)    end 

執行語句

SELECT    需轉換中文欄位, dbo.fn_GetPy(中文欄位) AS 列別名FROM        表名稱

 

 感謝：Luckeryin