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
*/