if OBJECT_ID('test') is not null drop table test go create table test ( id int, col1 varchar(5), col2 varchar(5) ) go insert test select 1,'A','A' union all select 2,'AB','BA' UNION ALL select 3,'ABD','DBA' union all select 4,'ABD','ACD' union all select 5,'A','B' GOif OBJECT_ID('fun_Match') is not null drop function fun_Match go create function fun_Match ( @col1 varchar(5), @col2 varchar(5) ) returns nvarchar(3) as begin declare @Result nvarchar(3) declare @a table(col1 varchar(1),col2 varchar(1)) if len(@col1)=1 and len(@col2)=1 and @col1=@col2 begin set @Result='一样' end else if len(@col1)=1 and len(@col2)=1 and @col1<>@col2 begin set @Result='不一样' end else begin while len(@col1)>1 begin insert into @a(col1,col2) values(left(@col1,1),left(@col2,1)) set @col1=right(@col1,len(@col1)-1) set @col2=right(@col2,len(@col2)-1) end insert into @a(col1,col2) values(@col1,@col2) if ((select count(1) from( select col1 from @a union all select col2 from @a)t)%(select count(1) from( select col1 from @a union select col2 from @a)t))<>0 begin set @Result='不一样' end else begin set @Result='一样' end end return @Result end goselect *,dbo.fun_match(col1,col2) as Match from test /* id col1 col2 Match ---------------------------------- 1 A A 一样 2 AB BA 一样 3 ABD DBA 一样 4 ABD ACD 不一样 5 A B 不一样 */
--借楼上的数据: CREATE FUNCTION judge_2Col(@a VARCHAR(5),@b VARCHAR(5)) RETURNS VARCHAR(20) AS BEGIN DECLARE @r VARCHAR(10) WHILE @a<>'' SELECT @b=REPLACE(@b,LEFT(@a,1),''), @a=RIGHT(@a,LEN(@a)-1) IF @a='' AND @b='' SET @r='一样' ELSE SET @r='不一样' RETURN @r END GO SELECT *, CASE WHEN LEN(col1)=LEN(col2) THEN dbo.judge_2col(col1,col2) ELSE 'g' end res FROM test--result /*id col1 col2 res ----------- ----- ----- -------------------- 1 A A 一样 2 AB BA 一样 3 ABD DBA 一样 4 ABD ACD 不一样 5 A B 不一样(所影响的行数为 5 行) */
drop table test
go
create table test
(
id int,
col1 varchar(5),
col2 varchar(5)
)
go
insert test
select 1,'A','A' union all
select 2,'AB','BA' UNION ALL
select 3,'ABD','DBA' union all
select 4,'ABD','ACD' union all
select 5,'A','B'
GOif OBJECT_ID('fun_Match') is not null
drop function fun_Match
go
create function fun_Match
(
@col1 varchar(5),
@col2 varchar(5)
)
returns nvarchar(3)
as
begin
declare @Result nvarchar(3)
declare @a table(col1 varchar(1),col2 varchar(1))
if len(@col1)=1 and len(@col2)=1 and @col1=@col2
begin
set @Result='一样'
end
else if len(@col1)=1 and len(@col2)=1 and @col1<>@col2
begin
set @Result='不一样'
end
else
begin
while len(@col1)>1
begin
insert into @a(col1,col2) values(left(@col1,1),left(@col2,1))
set @col1=right(@col1,len(@col1)-1)
set @col2=right(@col2,len(@col2)-1)
end
insert into @a(col1,col2) values(@col1,@col2)
if ((select count(1) from(
select col1 from @a
union all
select col2 from @a)t)%(select count(1) from(
select col1 from @a
union
select col2 from @a)t))<>0
begin
set @Result='不一样'
end
else
begin
set @Result='一样'
end
end
return @Result
end
goselect *,dbo.fun_match(col1,col2) as Match from test
/*
id col1 col2 Match
----------------------------------
1 A A 一样
2 AB BA 一样
3 ABD DBA 一样
4 ABD ACD 不一样
5 A B 不一样
*/
CREATE FUNCTION judge_2Col(@a VARCHAR(5),@b VARCHAR(5))
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @r VARCHAR(10)
WHILE @a<>''
SELECT @b=REPLACE(@b,LEFT(@a,1),''), @a=RIGHT(@a,LEN(@a)-1)
IF @a='' AND @b=''
SET @r='一样'
ELSE
SET @r='不一样'
RETURN @r
END
GO
SELECT *, CASE WHEN LEN(col1)=LEN(col2) THEN dbo.judge_2col(col1,col2) ELSE 'g' end res
FROM test--result
/*id col1 col2 res
----------- ----- ----- --------------------
1 A A 一样
2 AB BA 一样
3 ABD DBA 一样
4 ABD ACD 不一样
5 A B 不一样(所影响的行数为 5 行)
*/