SQL无序字符比较函数.docxVIP

go--创建函数(第一版)create?function?get_orderstr(@str?varchar(8000))returns?varchar(8000)asbegin????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?相同elseprint?不同--运行结果/*相同*/?--第二版/*?*?功能：不按先后顺序比较字符串序列是否相同???????*?*?适用：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?outputASBEGIN????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?outputEND?GO--测试示例declare?@ret?intexec?sp_CompareString?a,b,c,?b,c,a,?,,@ret outselect?@retexec?sp_CompareString?a,b,c,?b,c,c,a,?,,@ret outselect?@retdrop?proc?sp_CompareString?go--第三版/* * * * * * * * * * * * * * * * * * * * * * *?*?功能：不按先后顺序比较字符串序列是否相同*?*?适用：SQL Server 2000 / SQL Server 2005???*?*?返回：相同不相同??????????????????????*?*?作者：Limpire(昨夜小楼)???????????????????*?* * * * * * * * * * * * * * * * * * * * * * */--创建函数CREATE?FUNCTION?fn_CompareString(???