SQL Server write a function to obtain the pinyin code of Chinese Characters

SQL Server write a function to obtain the 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 it is not a Chinese character, the original character is returned.

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

Then (

Select top 1 PY

From

(

Select 'A' as PY, n' comment 'as word

Union all select 'B', n''

Union all select 'C', n' error'

Union all select 'D', N 'region'

Union all select 'E', n' then'

Union all select 'F', n' then'

Union all select 'G', n' then'

Union all select 'h', N 'hour'

Union all select 'J', n' else'

Union all select 'k', n' then'

Union all select 'l', n' then'

Union all select 'M', n' then'

Union all select 'n', n' then'

Union all select 'O', n' then'

Union all select 'P', n' exposure'

Union all select 'Q', n' then'

Union all select 'R', N 'region'

Union all select's ', N 'region'

Union all select 'T', n' then'

Union all select 'w', N 'hangzhou'

Union all select 'x', n' then'

Union all select 'y', N 'yun'

Union all select 'Z', n' else'

) 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 ('zhang san ')

-- Result: ZS