Compare If Two Strings Contain The Same Words

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

Solution 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@ttable (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 ONEXISTS (
    SELECT1FROM STRING_SPLIT(t1.val, ' ') s1
    LEFTJOIN STRING_SPLIT(t2.val, ' ') s2 ON s2.value = s1.value
    HAVINGCOUNT(CASEWHEN s2.value ISNULLTHEN1) =0ANDCOUNT(*) = (SELECTCOUNT(*) FROM STRING_SPLIT(t2.val, ' '))
);

SELECT*FROM@t t1
JOIN@t t2 ON (
        SELECT STRING_AGG(s1.value, ' ') WITHINGROUP (ORDERBY s1.value)
        FROM STRING_SPLIT(t1.val, ' ') s1
    ) = (
        SELECT STRING_AGG(s2.value, ' ') WITHINGROUP (ORDERBY s2.value)
        FROM STRING_SPLIT(t2.val, ' ') s2
    )
);

Solution 2:

It is relatively easy to implement by using T-SQL and XQuery. Specifically by using XQuery's quantified expressions.

Here is how it works:

1. Converting input world list into XML, i.e. tokenization process.
2. Running quantified expression. Sequential order of words is irrelevant.
3. Counting number of words in the source and the target.
4. Outcome of both (#2 and #3) is the final result.

SQL

-- DDL and sample data population, startDECLARE@tblTABLE( ID INTIDENTITYPRIMARY KEY, WordList1 VARCHAR(1024), WordList2 VARCHAR(1024));
INSERTINTO@tbl (WordList1, WordList2) VALUES
('one two three', 'two one three'),
('one two three', 'two   one   three  '),
('one two    three', ' one two two    three three');
-- DDL and sample data population, endDECLARE@SeparatorCHAR(1) = SPACE(1);

;WITH rs AS
(
    SELECT*
       , TRY_CAST('<root><source><r>'+ REPLACE(WordList1, @Separator, '</r><r>') +'</r></source>'+'<target><r>'+ REPLACE(WordList2, @Separator, '</r><r>') +'</r></target></root>'AS XML) AS xmldata
    FROM@tbl
)
SELECT* 
    , xmldata.value('every $x in /root/source/r[text()]/text()
                satisfies ($x = (/root/target/r[text()]/text())
              and (count(/root/source/r[text()]) eq count(/root/target/r[text()])))', 'BIT') ASresultFROM rs;

Output

+----+-----------------+---------------------------+--------+| ID |    WordList1    |         WordList2         |result|+----+-----------------+---------------------------+--------+|1|one two three   | two one three             |1||2|one two three   | two   one   three         |1||3|one two   three |one two two  three three |0|+----+-----------------+---------------------------+--------+

Solution 3:

--.... > SQL2017

declare@ttable(col varchar(400));
insertinto@t(col) values('one three two'), ('three   two one'), ('one two    three'), (' one two two    three three   ');

select*, 
(
/*
select string_agg(value, ' ') within group (order by value)
from openjson(concat('["', replace(string_escape( col, 'json' ) , ' ', '","'), '"]'))
where value <> ''
*/select string_agg(value, ' ') withingroup (orderbyvalue)
from 
(
selectdistinctvaluefrom openjson(concat('["', replace(string_escape(col, 'json' ) , ' ', '","'), '"]'))
wherevalue<>''
) as s
) as rearranged
from@t;