SQL query function based on the first letter of Chinese Characters

-- Reference the predecessors --
/***** Object: userdefinedfunction [DBO]. [f_getpy] script Date: 06/09/2010 10:31:33 ******/
Set ansi_nulls on
Go
Set quoted_identifier on
Go
Create Function [DBO]. [f_getpy] (@ STR nvarchar (4000 ))
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 into @ T (CHR, letter)
Select 'ay', 'A' Union all select '8', 'B' Union all
Select 'accept', 'C' Union all select 'accept', 'D' Union all
Select 'hangzhou', 'E' Union all select 'hangzhou', '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 'running', '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

 

-- -Test Data ---
If Object_id ( ' [Pactinfo] ' ) Is Not Null Drop Table [ Pactinfo ]
Go
Create Table [ Pactinfo ] ( [ ID ] Int , [ Pactname ] Varchar ( 4 ))
Insert [ Pactinfo ]
Select 1 , ' Normal ' Union All
Select 2 , ' China ' Union All
Select 3 , ' Cooking ' Union All
Select 4 , ' Sending and receiving '

---Query ---
Select
*
From
[Pactinfo]
Where
DBO. f_getpy (pactname) Like'Z %'

---Result ---
Id pactname
-------------------
1Normal
2China
3Cooking

(The number of affected rows is3Rows)