Create an SQL pinyin Function

Set ansi_nulls on
Set quoted_identifier on
Go

-- Create a pinyin Function
Alter function [DBO]. [f_getpy] (@ STR nvarchar (400 ))
Returns nvarchar (4000)
As
Begin
Declare @ strlen int, @ Re nvarchar (4000)
Declare @ t table (CHR nchar (1) Collate chinese_prc_ci_as, letter nchar (1 ))
Insert @ t select 'A', 'A' Union all select '8', 'B'
Union all select 'hour', 'C' Union all select 'hour', 'D'
Union all select 'hangzhou', 'E' Union all select ', 'F'
Union all select 'hangzhou', 'G' Union all select 'haid', 'H'
Union all select ', 'J' Union all select 'hour', 'K'
Union all select 'hangzhou', 'L' Union all select 'hangzhou', 'M'
Union all select 'hour', 'n' Union all select 'ou', 'O'
Union all select 'hangzhou', 'P' Union all select '7', 'Q'
Union all select 'hour', 'R' Union all select 'hour', 'S'
Union all select 'others', 't'union all select 'hour', 'W'
Union all select 'Xi ', 'x' Union all select' Ya ', 'y'
Union all select 'hour', 'Z'

select @ strlen = Len (@ Str ), @ Re = ''
while @ strlen> 0
begin
select top 1 @ Re = letter + @ Re, @ strlen = @ strlen-1
from @ t a where CHR <= substring (@ STR, @ strlen, 1)
order by chr desc
If @ rowcount = 0
select @ Re = substring (@ STR, @ strlen, 1) + @ Re, @ strlen = @ strlen-1
end
return (@ Re)
end