sql - Compare if two strings contain the same words

Question

I have two strings:

'one two three'

and

'two one three'

While they are not the same if I compare them directly the values that they contain separately are the same and this is what it is important for me. So my answer to the comparison will be that the strings are equal. I am looking for more straight forward way to compare them without the need create function to split and compare each separate value. Is there such solution?

I have found this thread in stackoverflow: How to compare if two strings contain the same words in T-SQL for SQL Server 2008?

It does not work for me because I want to make this comparison on the go in a cte where I make other comparisons.

Answer 1

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
    )
);