[MSSQL]无序字符比较函数

--创建函数(第一版)
create function get_orderstr(@str varchar(8000))
returns varchar(8000)
as
begin
    set @str=rtrim(@str)
    declare @tb table(s varchar(1),a int)
    while len(@str)>0
    begin
    insert into @tb select left(@str,1),ascii(left(@str,1))
    set @str=right(@str,len(@str)-1)
    end
    declare @sql varchar(8000)
    select @sql=isnull(@sql+'','')+s from @tb order by a
    return isnull(@sql,'')
end
    
--测试示例
if(dbo.get_orderstr('abc')=dbo.get_orderstr('acb'))
print '相同'
else 
print '不同'
--运行结果
/*
相同
*/

 

--第二版
/*
 * 功能：不按先后顺序比较字符串序列是否相同       *
 * 适用：SQL Server 2000 / SQL Server 2005         *
 * 返回：相同不相同                               *
 * 作者：Flystone                                  *
 * 描述：学习Limpire(昨夜小楼)的方法后做一个动态SQL的*
*/
go
--创建存储过程(这个不是函数)
CREATE proc sp_CompareString
 @Str1 varchar(100),
 @Str2 varchar(100),
 @Split varchar(10),
 @ret int output
AS
BEGIN
    declare @Len int, @Sub varchar(100)
    if @Str1 = @Str2 return(1)
    if len(@Str1) <> len(@Str2) or len(replace(@Str1, @Split, '')) <> len(replace(@Str2, @Split, '')) 
    begin
       set @ret = 0
       return
    end 
    set @str1 = 'select '''+replace(@str1,@Split,''' as col union all select ''')+''''
    set @str2 = 'select '''+replace(@str2,@Split,''' as col union all select ''')+''''
    declare @s nvarchar(4000)
    set @s = '
    if exists(select 1 from ('+@str1+') a where not exists(select 1 from ('+@str2+') b where a.col  = b.col)
    or
    exists(select 1 from ('+@str2+') a where not exists(select 1 from ('+@str1+') b where a.col  = b.col)
    ))
       select @ret = 0
    else 
       select @ret = 1'
    exec sp_executesql @s,N'@ret int output',@ret output
END
GO
--测试示例
declare @ret int
exec sp_CompareString 'a,b,c', 'b,c,a', ',',@ret out
select @ret
exec sp_CompareString 'a,b,c', 'b,c,c,a', ',',@ret out
select @ret
drop proc sp_CompareString 
go
--第三版
/* * * * * * * * * * * * * * * * * * * * * * *
 * 功能：不按先后顺序比较字符串序列是否相同*
 * 适用：SQL Server 2000 / SQL Server 2005   *
 * 返回：相同不相同                      *
 * 作者：Limpire(昨夜小楼)                   *
 * * * * * * * * * * * * * * * * * * * * * * */
--创建函数
CREATE FUNCTION fn_CompareString
(
    @Str1 varchar(100), 
    @Str2 varchar(100), 
    @Split varchar(10)
)
RETURNS bit
AS
BEGIN
    declare @Len int, @Sub varchar(100)
    if @Str1 = @Str2 return(1)
    if len(@Str1) <> len(@Str2) or len(replace(@Str1, @Split, '')) <> len(replace(@Str2, @Split, '')) return(0)
    select @Len = len(@Split), @Str1 = @Split + @Str1 + @Split, @Str2 = @Split + @Str2 + @Split
    while charindex(@Split, @Str1, @Len + 1) > 0
       begin
           set @Sub = left(@Str1, charindex(@Split, @Str1, @Len + 1) + @Len - 1)
           if charindex(@Sub, @Str2) = 0 return(0)
           while charindex(@Sub, @Str1) > 0 set @Str1 = replace(@Str1, @Sub, ',')
           while charindex(@Sub, @Str2) > 0 set @Str2 = replace(@Str2, @Sub, ',')
           if len(@Str1)<>len(@Str2) return(0)
       end
    return(1)
END
GO
--测试示例
select dbo.fn_CompareString('a,b,c', 'b,c,a', ',') 
select dbo.fn_CompareString('a,b,c', 'b,c,c,a', ',') 

--运行结果
/*
1
0
*/