Create a query and execute the following statement to generate the function fn_GetPy.
Copy codeThe Code is as follows:
-- Generate the first pinyin code
CREATE function fn_GetPy (@ str nvarchar (4000 ))
Returns nvarchar (4000)
-- WITH ENCRYPTION
As
Begin
Declare @ intLen int
Declare @ strRet nvarchar (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
........................................ .....
Test:
SELECT Product_ID, dbo. fn_GetPy (Product_Name) AS pymc
FROM dbo. T_Product