SQL Server field extract the first letter of Pinyin

Currently, I have encountered a situation where I need to extract the first letter of a field in SQL Server, which consists of Chinese characters, English letters, numbers, and "-". Baidu has a bunch, find the following method and record it for later use!

First, create a function.

 --  Generate the first pinyin code    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 ) > =    '  Bytes  '    Then   '  Z  '      When    Substring ( @ Str , @ Intlen , 1 ) > =    '  Ya  '    Then    '  Y  '     When    Substring ( @ Str , @ Intlen , 1 ) > =    '  XI  '    Then    '  X  '      When    Substring ( @ Str ,@ Intlen , 1 ) > =    '  Bytes  '    Then    '  W  '      When    Substring ( @ Str , @ Intlen , 1 )> =    '  He  '    Then    '  T  '      When    Substring ( @ Str , @ Intlen , 1 ) > =    '  Bytes '    Then    '  S  '      When    Substring ( @ Str , @ Intlen , 1 ) > =    '  Bytes  '    Then    ' R  '      When    Substring ( @ Str , @ Intlen , 1 ) > =    '  VII.  '    Then    '  Q  '      When   Substring ( @ Str , @ Intlen , 1 ) > =    '  Bytes  '    Then    '  P  '      When    Substring ( @ Str , @ Intlen , 1 ) > =    '  Oh  '    Then    '  O  '      When    Substring ( @ Str , @ Intlen , 1 ) > =   '  Bytes  '    Then    '  N  '      When    Substring ( @ Str , @ Intlen , 1 ) > =    '  Bytes  '   Then    '  M  '      When    Substring ( @ Str , @ Intlen , 1 ) > =    '  Bytes  '    Then    '  L '      When    Substring ( @ Str , @ Intlen , 1 ) > =    '  Bytes  '    Then    '  K  '      When    Substring (@ Str , @ Intlen , 1 ) > =    '  Development  '    Then      '  J  '      When    Substring ( @ Str , @ Intlen ,1 ) > =    '  Havip  '    Then    '  H  '      When    Substring ( @ Str , @ Intlen , 1 ) > =    ' Bytes  '    Then    '  G  '      When    Substring ( @ Str , @ Intlen , 1 ) > =    '  Sending  '    Then   '  F  '      When    Substring ( @ Str , @ Intlen , 1 ) > =    '  Bytes  '    Then    '  E  '     When    Substring ( @ Str , @ Intlen , 1 ) > =    '  Bytes  '    Then    '  D  '      When    Substring ( @ Str ,@ Intlen , 1 ) > =    '  Bytes  '    Then    '  C  '      When    Substring ( @ Str , @ Intlen , 1 )> =    '  8  '    Then    '  B  '      When    Substring ( @ Str , @ Intlen , 1 ) > =    '  Ya '    Then    '  A  '      Else    Rtrim ( Ltrim ( Substring ( @ Str , @ Intlen , 1  )))  End      --  For special Chinese characters, no pinyin code is generated   If ( ASCII ( @ Temp ) >  127 ) Set    @ Temp    =    ''      --  No pinyin code is generated for parentheses.    If    @ Temp    =    '  ( '    Or    @ Temp    =    '  )  '    Set    @ Temp    =    ''      Select    @ Strret    =    @ Temp    +    @ Strret      Set   @ Intlen    =    @ Intlen    -    1      End      Return    Lower ( @ Strret  )  End  

Execution statement

 
SelectYou need to convert the Chinese field, DBO. fn_getpy (Chinese field)AsColumn aliasFromTable Name
 

 

Thank you: luckeryin