SQLServer实现Oracle中的translate函数

在看SQLCOOKBOOK的时候，里面涉及到提取纯英文或者提取纯中文，ORACLE因为有translate函数的关系可以很方便的进行剔除。但是放到SQLSERVER中，总会提及没有translate函数所以无法实现。最后烦了，思考了一下自己写了一个出来

本来考虑直接一个个replace就行了，后来发现自己too simple。因为replace的时候是有先后顺序的，比如把‘ab’translate成'ba'，等你把‘a’replace成‘b’的时候，就无法继续把'b'replace成'a'，结果不符。

能想到的解决方案就是进行substring分割，然后在结果链接起来。不过需要在内部复制一个字符串。一个字符串用于查找位置，然后在复制上进行更改。

现在已经实现了

/*
例：
declare @before nvarchar(500),@end nvarchar(10),@result nvarchar(500),@i int
set @result ='456789中文789456'
set @before='456789'
set @end ='789456'

select  dbo.translate(@result,@before,@end)

结果：

789456中文456789

即，将4变成7，将5变成8，将6变成9，将7变成4，将8变成5，将9变成6

如果第二个参数（@before）多余第三个参数(@end)
会将@before多余的字符全部置为空

例：
declare @before nvarchar(500),@end nvarchar(10),@result nvarchar(500),@i int
set @result ='456789中文12315646546中国789456'
set @before='0123456789'
set @end =''

select  dbo.translate(@result,@before,@end)

结果：

中文中国

*/

以下是源代码：

if exists(select 1 from sysobjects where name ='translate')

begin
drop FUNCTION translate
end
go
create FUNCTION  translate
(
@result nvarchar(500),
@from nvarchar(500),
@to nvarchar(500)
)
returns nvarchar(500)
as
begin
         declare @i int ,@j int,@result1 nchar(500)

         if(len(@from)=len(@to))
         begin
              set @result1=@result
              set @i=1
         while @i<=LEN(@from)
         begin
             set @j=0;
             while charindex(substring(@from,@i,1),@result,@j)>0
             begin
               set @j=charindex(substring(@from,@i,1),@result,@j)
               set @result1=left(@result1,@j-1)+substring(@to,@i,1)+substring(@result1,@j+1,len(@result1));
               set @j=@j+1
             end
             set @i=@i+1
          
         end
         end
         else if(len(@from)>len(@to))
              begin
                   set @i=1
                   declare @duoyu nchar(500)
                   set @duoyu=right(@from,len(@from)-len(@to))
                   while @i<=len(@duoyu)
                   begin
                     set @result=replace(@result,substring(@duoyu,@i,1),'')
                     set @i=@i+1;
                   end
                   set @result1=@result
                   set @i=1
                   while @i<=LEN(@to)
                   begin
                       set @j=0;
                       while charindex(substring(@from,@i,1),@result,@j)>0
                       begin
                            set @j=charindex(substring(@from,@i,1),@result,@j)
                            set @result1=left(@result1,@j-1)+substring(@to,@i,1)+substring(@result1,@j+1,len(@result1));
                            set @j=@j+1
                       end
                       set @i=@i+1
                   end
              end
         return @result1
end