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 ='' @ 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)> = '?? 'Then'r' When substring (@ str, @ intLen, 1)> = '7' then'q' When substring (@ str, @ intLen, 1)> = '? R 'then' P' When substring (@ str, @ intLen, 1)> = 'ou' then' O' When substring (@ str, @ intLen, 1)> = '?? 'Then'n' When substring (@ str, @ intLen, 1)> = '? ''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)> = '?? 'Then'e' When substring (@ str, @ intLen, 1)> = '?? '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: Stored Procedure 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 it is not a Chinese character, the original character is returned. 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'' Union all select 'C', n '? ' Union all select 'D', n '? ' Union all select 'E', n '?? ' Union all select 'F', n '? ' Union all select 'G', n '? ' Union all select 'h', n '?? ' Union all select 'J', n '? H' Union all select 'k', n '? ' Union all select 'l', n '? ' Union all select 'M', n '?? ' Union all select 'n', n '?? ' Union all select 'O', n '? A' 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 '? ' Union all select 'Z', n '?? ' ) 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 |