Create a query that executes the following statement generation function fn_getpy
Copy Code code as follows:
--Generate phonetic first 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) >= ' 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
。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
Test:
SELECT product_id, Dbo.fn_getpy (product_name) as PYMC
FROM dbo. T_product