SQL Server writes a function to get the phonetic code of Chinese characters

SQL Server writes a function to get Chinese pinyin code (first letter)

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)

--If non-Chinese characters, return the original character

Set @PY = @PY + (case when Unicode (@word) between 19968 and 19968+20901

Then (

Select top 1 PY

From

(

Select ' A ' as Py,n ' ao ' as word

UNION ALL SELECT ' B ', N ' book '

UNION ALL SELECT ' C ', N ' wrong '

UNION ALL SELECT ' D ', N ' 鵽 '

UNION ALL SELECT ' E ', N ' 樲 '

UNION ALL SELECT ' F ', N ' 鳆 '

UNION ALL SELECT ' G ', N ' hiker '

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 ' temperature '

UNION ALL SELECT ' n ', n ' 桛 '

UNION ALL SELECT ' O ', N ' retting '

UNION ALL SELECT ' P ', N ' exposure '

UNION ALL SELECT ' Q ', N ' 囕 '

UNION ALL SELECT ' R ', N ' 鶸 '

UNION ALL SELECT ' S ', N ' 蜶 '

UNION ALL SELECT ' T ', N ' sheath '

UNION ALL SELECT ' W ', N ' Mo '

UNION ALL SELECT ' X ', N ' 鑂 '

UNION ALL SELECT ' Y ', N ' rhyme '

UNION ALL SELECT ' Z ', N ' out '

) 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

--Call

Select Dbo.fun_getpy (' John ')

--Results: ZS