Copy Code code as follows:
--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) >= ' he ' then ' T '
When substring (@str, @intLen, 1) >= ' sa ' then ' S '
When substring (@str, @intLen, 1) >= ' 呥 ' Then ' R '
When substring (@str, @intLen, 1) >= ' seven ' then ' Q '
When substring (@str, @intLen, 1) >= ' 妑 ' Then ' P '
When substring (@str, @intLen, 1) >= ' Oh ' Then ' O '
When substring (@str, @intLen, 1) >= ' 拏 ' Then ' N '
When substring (@str, @intLen, 1) >= ' 嘸 ' Then ' M '
When substring (@str, @intLen, 1) >= ' garbage ' then ' L '
When substring (@str, @intLen, 1) >= ' ka ' Then ' K '
When substring (@str, @intLen, 1) >= ' not ' then ' J '
When substring (@str, @intLen, 1) >= ' hafnium ' Then ' H '
When substring (@str, @intLen, 1) >= ' there ' then ' G '
When substring (@str, @intLen, 1) >= ' fa ' then ' F '
When substring (@str, @intLen, 1) >= ' 妸 ' Then ' E '
When substring (@str, @intLen, 1) >= ' 咑 ' Then ' D '
When substring (@str, @intLen, 1) >= ' cha ' Then ' C '
When substring (@str, @intLen, 1) >= ' eight ' then ' B '
When substring (@str, @intLen, 1) >= ' acridine ' Then ' A '
else RTrim (LTrim (substring (@str, @intLen, 1))
End
--for special characters of Chinese characters, no phonetic code is generated
if (ASCII (@temp) >127) Set @temp = '
--For English brackets, no phonetic code is generated
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 (' John ')
--Back: ZS
For! : 2:
A stored procedure to take the first letter of Chinese 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)
--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 ' 驁 ' 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 ' 漚 '
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 ' 籜 '
UNION ALL SELECT ' W ', N ' 鶩 '
UNION ALL SELECT ' X ', N ' 鑂 '
UNION ALL SELECT ' Y ', N ' rhythmic '
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