SQL比较两个字符串中的字符

Question

如何比较T-SQL(SQL Server(中的两个字符串，以确定它们是否包含相同的字符而不按相同的顺序排列。

例如：

相同

'671'
'716'

相同

'671'
'671'

不相同的

'671'
'731'

Answer 1

这真的很痛苦。一种方法是分解字符并将其作为表格进行比较：

with cte1 as (
select left(v.s, 1) as l, stuff(v.s, 1, 1, '') as rest
from (values ('671')) v(s)
union all
select left(rest, 1) as l, stuff(rest, 1, 1, '') as rest
from cte1
where rest <> ''
),
cte2 as (
select left(v.s, 1) as l, stuff(v.s, 1, 1, '') as rest
from (values ('716')) v(s)
union all
select left(rest, 1) as l, stuff(rest, 1, 1, '') as rest
from cte2
where rest <> ''
)
select (case when count(*) > 0 then 'NOT SAME' else 'SAME' end)
from (select l, sum(in1) as in1, sum(in2) as in2
from ((select l, 1 as in1, 0 as in2 from cte1) union all
(select l, 0 as in1, 1 as in2 from cte2) 
) i
group by l
) l
where in1 <> in2

Answer 2

这里有一个函数，它有两个字符串作为输入参数。该函数将字符串分解为表变量，并检查不同字符的计数是否相同，以及两个表之间的联接是否返回相同的计数。

因此，它将761和16767视为相同的字符串。如果您希望字符串的长度相等，只需删除不同的

CREATE FUNCTION dbo.CompareStrings (@str1 VARCHAR(50), @str2 varchar(50))
returns VARCHAR(50)
BEGIN
DECLARE @len1    INT,
@len2    INT,
@cnt1    INT =1,
@cnt2    INT =1,
@char1   VARCHAR(1)='',
@char2   VARCHAR(1)='',
@match bit = 0,
@output VARCHAR(50)='Not same',
@count1 int,
@count2 int,
@count_match int               
declare @string1 table (alpha varchar(1))
declare @string2 table (alpha varchar(1))
SELECT @len1 = Len(@str1)
WHILE @cnt1 <= @len1
BEGIN
SELECT @char1 = Substring(@str1, @cnt1, 1) 
INSERT INTO @string1(alpha) values (@char1)
SET @cnt1+=1
END
SELECT @len2 = Len(@str2)
WHILE @cnt2 <= @len2
BEGIN
SELECT @char2 = Substring(@str2, @cnt2, 1) 
INSERT INTO @string2(alpha) values (@char2)
SET @cnt2+=1
END
select @count1 = count(distinct alpha) from @string1
select @count2 = count(distinct alpha) from @string2
select @count_match = count(distinct t1.alpha) from @string1 t1 inner 
join @string2 t2 on t1.alpha = t2.alpha
if (@count1 = @count2 AND @count1 = @count_match) 
set  @match = 1

if (@match =1)
set @output = 'Same'

RETURN @output
END

Answer 3

这里有一个方法只有当你有3个字符作为你的样本时才有效

DECLARE @T TABLE
(
V1 VARCHAR(10),
V2 VARCHAR(10)
);
INSERT INTO @T VALUES
('123', '312'),
('671', '176'),
('123', '341');
SELECT CASE WHEN
REPLACE(
REPLACE(
REPLACE(V1, SUBSTRING(V2, 1, 1), ''),
SUBSTRING(V2, 2, 1), ''
),
SUBSTRING(V2, 3, 1), ''
) = '' THEN 'SAME' ELSE 'NOT SAME' END Result
FROM @T;

退货：

+----------+
|  Result  |
+----------+
| SAME     |
| SAME     |
| NOT SAME |
+----------+

或者通过创建类似的功能

CREATE FUNCTION IsSame 
(
@FStr VARCHAR(100), @SStr VARCHAR(100)
) 
RETURNS VARCHAR(8)
AS
BEGIN
DECLARE @I INT = 1;
DECLARE @R VARCHAR(8) = 'NOT SAME';
IF LEN(@FStr) <> LEN(@SStr)
GOTO NotSame
ELSE
BEGIN
WHILE @I <= LEN(@SStr)
BEGIN
SET @FStr = (SELECT REPLACE(@FStr, SUBSTRING(@SStr, @I, 1), ''));
SET @I = @I + 1;
END
END
IF @FStr = ''
SET @R = 'SAME';    
NotSame:
RETURN @R;
END
GO

然后你可以把它用作

SELECT dbo.IsSame('123', '312');