【叶子函数分享五十】无序字符比较函数

go

--创建函数(第一版)

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

*/