SQL Server field extract Pinyin initials

At present, a situation encountered in the work, you need to extract a field in SQL Server Pinyin first letter, the field by the Chinese characters, English, digital and "-" composition, Baidu a bunch, find the following method, record, for later use!

First set up a function

--generate pinyin first code  CREATE  functionFn_getpy (@str  nvarchar(4000))    returns  nvarchar(4000)    --With encryption   as    begin    Declare  @intLen int    Declare  @strRet nvarchar(4000)    Declare  @temp  nvarchar( -)    Set  @intLen  =  Len(@str)    Set  @strRet  =  "'     while  @intLen  >  0    begin    Set  @temp  =  "'    Select  @temp  =   Case       when  substring(@str,@intLen,1)>=  'as'   Then  'Z'     when  substring(@str,@intLen,1)>=  'ya'   Then  'Y'     when  substring(@str,@intLen,1)>=  'XI'   Then  'X'     when  substring(@str,@intLen,1)>=  '屲'   Then  'W'     when  substring(@str,@intLen,1)>=  'his'   Then  'T'     when  substring(@str,@intLen,1)>=  'SA'   Then  'S'     when  substring(@str,@intLen,1)>=  '呥'   Then  'R'     when  substring(@str,@intLen,1)>=  'Seven'   Then  'Q'     when  substring(@str,@intLen,1)>=  '妑'   Then  'P'     when  substring(@str,@intLen,1)>=  'Oh'   Then  'O'     when  substring(@str,@intLen,1)>=  'Hallasan'   Then  'N'     when  substring(@str,@intLen,1)>=  '嘸'   Then  'M'     when  substring(@str,@intLen,1)>=  ' Garbage'   Then  'L'     when  substring(@str,@intLen,1)>=  'ka'   Then  'K'     when  substring(@str,@intLen,1)>=  ' not'   Then    'J'     when  substring(@str,@intLen,1)>=  'hafnium'   Then  'H'     when  substring(@str,@intLen,1)>=  'Xu'   Then  'G'     when  substring(@str,@intLen,1)>=  'Hair'   Then  'F'     when  substring(@str,@intLen,1)>=  'Ehegan'   Then  'E'     when  substring(@str,@intLen,1)>=  'Otah'   Then  'D'     when  substring(@str,@intLen,1)>=  'Cha'   Then  'C'     when  substring(@str,@intLen,1)>=  'Eight'   Then  'B'     when  substring(@str,@intLen,1)>=  'Acridine'   Then  'A'    Else  RTrim(LTrim(substring(@str,@intLen,1)))    End    --for kanji special characters, no phonetic codes are generated  if(ASCII(@temp)>127)Set  @temp  =  "'    --for English parentheses, no phonetic codes are generated  if  @temp  =  '('  or  @temp  =  ')'  Set  @temp  =  "'    Select  @strRet  =  @temp  +  @strRet    Set  @intLen  =  @intLen  -  1    End    return  Lower(@strRet)    End 

EXECUTE statement

SELECT     as Column Aliases  from        Table name

Thank you: Luckeryin

SQL Server field extract Pinyin initials