Classic SQL Server user-defined function: query of the first character in pinyin

Classic SQL Server user-defined function: query of the first character in pinyin

/* -- Obtain the first letter of a Chinese character string According to the strong posts, I changed the two strong functions into a function. It can be applied to the query of mnemonic codes. Author: zhujian --*/ If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [fgetpy] ') and xtype in (n'fn', n'if', n'tf ')) Drop function [DBO]. [fgetpy] Go -- Create a pinyin Function Create Function fgetpy (@ STR varchar (500) = '') Returns varchar (500) As Begin Declare @ strlen int, @ return varchar (500), @ II int Declare @ n int, @ C char (1), @ CHN nchar (1) Select @ strlen = Len (@ Str), @ return = '', @ II = 0 Set @ II = 0 While @ II <@ strlen Begin Select @ II = @ II + 1, @ n = 63, @ CHN = substring (@ STR, @ II, 1) If @ CHN> 'Z' Select @ n = @ n + 1 , @ C = case CHN when @ CHN then Char (@ n) else @ C end From ( Select top 27 * from ( Select CHN = 'ay' Union all select '8' Union all select 'signature' Union all select 'signature' Union all select 'signature' Union all select 'output' Union all select 'signature' Union all select 'haid' Union all select '-- because have no' I' Union all select 'stop' Union all select 'signature' Union all select 'signature' Union all select 'signature' Union all select 'signature' Union all select 'ou' Union all select 'signature' Union all select '7' Union all select 'signature' Union all select 'signature' Union all select 'others' Union all select 'hour' -- no 'U' Union all select 'hour' -- no 'V' Union all select 'signature' Union all select 'Xi' Union all select 'ya' Union all select 'signature' Union all select @ CHN) as Order by CHN collate chinese_prc_ci_as ) As B Else set @ C = 'A' Set @ return = @ return + @ C End Return (@ return) End Go -- Test Select DBO. fgetpy ('wei baoguang ') as name pinyin, DBO. fgetpy (' AB 中 C') as Chinese Select DBO. fgetpy ('Liu ziliang ') as name pinyin, DBO. fgetpy (' AB Chinese c ') as Chinese Select DBO. fgetpy ('wu Ta') as name pinyin, DBO. fgetpy ('AB Chinese C') as Chinese Select DBO. fgetpy ('northeast ') as name pinyin, DBO. fgetpy (' AB 中 C') as Chinese -- Delete pinyin Functions Drop function fgetpy

 

Vbs

<Script language = VBScript>
<! --
''''''''''''''''''''''
'Provider: Xiaobai
'Contact method:
'E-mail: xiaobai@17560.net
'Welcome to test
'The issues found by chenjf users
'Tom made a modification.
'In addition, the reply to Liu Weiqiang is: this function can only get at present ..? Haha
'If there is something wrong, please ask for it or help improve it.
'Thank you
''''''''''''''''''''''''''''''
Function getpychar (char)
TMP = 65536 + ASC (char)
If (TMP> = 45217 and TMP <= 45252) then
Getpychar = ""
Elseif (TMP & gt; = 45253 and TMP <= 45760) then
Getpychar = "B"
Elseif (TMP & gt; = 45761 and TMP <= 46317) then
Getpychar = "C"
Elseif (TMP & gt; = 46318 and TMP <= 46825) then
Getpychar = "D"
Elseif (TMP & gt; = 46826 and TMP <= 47009) then
Getpychar = "e"
Elseif (TMP & gt; = 47010 and TMP <= 47296) then
Getpychar = "F"
Elseif (TMP & gt; = 47297 and TMP <= 47613) then
Getpychar = "G"
Elseif (TMP & gt; = 47614 and TMP <= 48118) then
Getpychar = "H"
Elseif (TMP & gt; = 48119 and TMP <= 49061) then
Getpychar = "J"
Elseif (TMP & gt; = 49062 and TMP <= 49323) then
Getpychar = "K"
Elseif (TMP & gt; = 49324 and TMP <= 49895) then
Getpychar = "L"
Elseif (TMP & gt; = 49896 and TMP <= 50370) then
Getpychar = "M"
Elseif (TMP & gt; = 50371 and TMP <= 50613) then
Getpychar = "N"
Elseif (TMP & gt; = 50614 and TMP <= 50621) then
Getpychar = "O"
Elseif (TMP & gt; = 50622 and TMP <= 50905) then
Getpychar = "P"
Elseif (TMP & gt; = 50906 and TMP <= 51386) then
Getpychar = "Q"
Elseif (TMP & gt; = 51387 and TMP <= 51445) then
Getpychar = "R"
Elseif (TMP & gt; = 51446 and TMP <= 52217) then
Getpychar = "S"
Elseif (TMP & gt; = 52218 and TMP <= 52697) then
Getpychar = "T"
Elseif (TMP & gt; = 52698 and TMP <= 52979) then
Getpychar = "W"
Elseif (TMP & gt; = 52980 and TMP <= 53640) then
Getpychar = "X"
Elseif (TMP & gt; = 53689 and TMP <= 54480) then
Getpychar = "Y"
Elseif (TMP & gt; = 54481 and TMP <= 62289) then
Getpychar = "Z"
Else' if it is not Chinese, it will not be processed
Getpychar = char
End if
End Function

Function getpy (STR)
For I = 1 to Len (STR)
Getpy = getpy & getpychar (mid (STR, I, 1 ))
Next
End Function
D = "query only"
Msgbox getpy (d)
// -->
</SCRIPT>