You have to split the strings because otherwise how do you compare separate parts.
I'm assuming you want to find all matching pairs of items. I've shown this with a self-join from one table, but you could equally do it from two.
This is a question of Relational Division Without Remainder, for which there are a number of solutions.
DECLARE @t table (val varchar(100));
INSERT @t(col) values('one three two'), ('three two one'), ('one two three'), (' one two two three three ');
SELECT *
FROM @t t1
JOIN @t t2 ON EXISTS (
SELECT 1
FROM STRING_SPLIT(t1.val, ' ') s1
LEFT JOIN STRING_SPLIT(t2.val, ' ') s2 ON s2.value = s1.value
HAVING COUNT(CASE WHEN s2.value IS NULL THEN 1) = 0
AND COUNT(*) = (SELECT COUNT(*) FROM STRING_SPLIT(t2.val, ' '))
);
SELECT *
FROM @t t1
JOIN @t t2 ON (
SELECT STRING_AGG(s1.value, ' ') WITHIN GROUP (ORDER BY s1.value)
FROM STRING_SPLIT(t1.val, ' ') s1
) = (
SELECT STRING_AGG(s2.value, ' ') WITHIN GROUP (ORDER BY s2.value)
FROM STRING_SPLIT(t2.val, ' ') s2
)
);
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…