/**/
/*
* 功能:不按先后顺序比较字符串序列是否相同 *
* 适用:SQL Server 2000 / SQL Server 2005 *
* 返回:1相同0不相同 *
* 作者:Flystone *
* 描述:学习Limpire(昨夜小楼)的方法后做一个动态SQL的 *
*/
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
/**/ /*
-----------
1
(所影响的行数为 1 行)
-----------
0
(所影响的行数为 1 行)
*/
* 功能:不按先后顺序比较字符串序列是否相同 *
* 适用:SQL Server 2000 / SQL Server 2005 *
* 返回:1相同0不相同 *
* 作者:Flystone *
* 描述:学习Limpire(昨夜小楼)的方法后做一个动态SQL的 *
*/
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
/**/ /*
-----------
1
(所影响的行数为 1 行)
-----------
0
(所影响的行数为 1 行)
*/