IF exists (Select * from dbo.sysObjects where id = Object_id (n '[dbo]. [f_getpy]') and xtype in (n'fn ', n'if ", n')) DROP FUNCTION [DBO] [f_getpy] Go
- Create function takes phonetic create function f_GetPy (@Str nvarchar (400)) returns nvarchar (4000) asbegin declare @strlen int, @ re nvarchar (4000) declare @t table (chr nchar (1) collate Chinese_PRC_CI_AS, letter nchar ( 1)) Insert @t select '', 'a' union all select 'eight', 'b' union all select '嚓', 'c' union all select '咑', 'D' union all succ '妸' , 'E' union all select ',' f 'union all select' 旮 ',' g 'union all select' ',' h 'union all select' 丌 ',' J 'Union All Select' 丌 ' , 'K' Union All SELECT 'FD', 'L' Union All Select '呒', 'M' Union All Select 'N,' N 'Union All Select' 噢 ',' O 'Union All SELECT' 妑 ' , 'P' Union All Select '7', 'Q' Union All Select '呥', 'R' Union All Select '仨', 'S' Union All Select 'He', 'T' Union All SELECT '屲' , 'W' Union All Select 'Xun', 'X' Union All Select '丫', 'Y' Union All SELECT '帀', 'Z'
SELECT @ Strlen = LEN (@str), @ re = '' while @strlen> 0 Begin select top 1 @ RE = Letter @ RE, @ Strlen = @ Strlen-1 from @ta where chr <= substring (@str, @ Strlen, 1) Order by chr desc if @@ rowcount = 0 Select @ RE = Substring (@ Str, @ strlen, 1) @ RE, @ Strlen = @ strlen-1 End return (@re) endGO
