比较两个字符串是否包含相同的单词 [英] Compare if two strings contain the same words

问题描述

我有两个字符串:

'一二三'

和

'二一三'

虽然如果我直接比较它们，它们并不相同，但它们分别包含的值是相同的，这对我来说很重要.所以我对比较的回答是字符串相等.我正在寻找更直接的方法来比较它们，而无需创建函数来拆分和比较每个单独的值.有没有这样的解决方案?

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?

我在 stackoverflow 中找到了这个线程:如何在T-SQL for SQL Server 2008 中比较两个字符串是否包含相同的单词?

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

它对我不起作用，因为我想在进行其他比较的 cte 中进行这种比较.

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

推荐答案

使用 T-SQL 和 XQuery 实现起来相对容易.特别是通过使用 XQuery 的量化表达式.

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

这是它的工作原理:

1. 将输入的世界列表转换为 XML，即标记化过程.
2. 运行量化表达.单词的顺序无关紧要.
3. 计算源和目标中的单词数.
4. 两者(#2 和 #3)的结果是最终结果.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE( ID INT IDENTITY PRIMARY KEY, WordList1 VARCHAR(1024), WordList2 VARCHAR(1024));
INSERT INTO @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, end

DECLARE @Separator CHAR(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') AS result
FROM rs;

输出

+----+-----------------+---------------------------+--------+
| 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 |
+----+-----------------+---------------------------+--------+

这篇关于比较两个字符串是否包含相同的单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！