Function for obtaining letters from the Chinese character and Pinyin headers in the Stored Procedure

-- Function

Create Function fn_getpy (@ STR nvarchar (4000 ))
Returns nvarchar (4000)
-- With encryption
As
Begin
Declare @ intlenint
Declare @ strretnvarchar (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)> = 'then' Z'
When substring (@ STR, @ intlen, 1)> = 'ya 'then' y'
When substring (@ STR, @ intlen, 1)> = 'Xi 'Then 'X'
When substring (@ STR, @ intlen, 1)> = 'then' W'
When substring (@ STR, @ intlen, 1)> = 'others'
When substring (@ STR, @ intlen, 1)> = 'thens'
When substring (@ STR, @ intlen, 1)> = 'duration' then 'R'
When substring (@ STR, @ intlen, 1)> = '7' then'q'
When substring (@ STR, @ intlen, 1)> = 'ping' then 'P'
When substring (@ STR, @ intlen, 1)> = 'ou' then' o'
When substring (@ STR, @ intlen, 1)> = 'then' n'
When substring (@ STR, @ intlen, 1)> = 'ping' then 'M'
When substring (@ STR, @ intlen, 1)> = 'then' L'
When substring (@ STR, @ intlen, 1)> = 'then' K'
When substring (@ STR, @ intlen, 1)> = '7' then'j'
When substring (@ STR, @ intlen, 1)> = 'hagh' then 'H'
When substring (@ STR, @ intlen, 1)> = 'then' G'
When substring (@ STR, @ intlen, 1)> = 'qout' then 'F'
When substring (@ STR, @ intlen, 1)> = 'ping' then'e'
When substring (@ STR, @ intlen, 1)> = 'followed 'thend'
When substring (@ STR, @ intlen, 1)> = 'ping' then 'C'
When substring (@ STR, @ intlen, 1)> = '8' then 'B'
When substring (@ STR, @ intlen, 1)> = 'A' 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
Go

-- Call
Select DBO. fn_getpy ('zhang san ')

-- Return Value: Zs

Answer! : 2 : Process of storing the first letter of Chinese characters and Pinyin

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)

-- returns the original character if it is not a Chinese character
set @ py = @ py + (case when Unicode (@ word) between 19968 and 19968 + 20901
then (
select top 1 py
from
(
select 'A' as py, N 'hour' as word
Union all select 'B', n' '
Union all select 'C ', N 'region'
Union all select 'D', N 'region'
Union all select 'E ', N 'hangzhou'
Union all select 'F', N 'hangzhou'
Union all select 'G ', N 'region'
Union all select 'h', N 'region'
Union all select 'J ', N 'hangzhou'
Union all select 'k', N 'hangzhou'
Union all select 'l ', N 'hangzhou'
Union all select 'M', N 'hangzhou'
Union all select 'n ', N 'region'
Union all select 'O', N 'region'
Union all select 'P ', n' exposure '
Union all select 'Q', n' comment'
Union all select 'R ', N 'region'
Union all select's ', N 'region'
Union all select 'T ', N 'hangzhou'
Union all select 'w', N 'hangzhou'
Union all select 'x ', N 'hour'
Union all select 'y', n' comment '
Union all select 'Z', n' comment'
) 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