sql - 比较 SQL Server 中由特殊字符分隔的值

Question

我有 2 列，比如说 COLA 和 COLB，数据如下:

COLA              | COLB
------------------+------------------
PLATE|SPOON|GLASS | PLATE|GLASS|SPOON
PLATE             | SPOON
OIL|JUG|MAT       | JUG|MAT
SPOON             | SPOON
OIL|MAT           | MAT|OIL

我试图返回不匹配的行，无论顺序如何。

预期输出:

COLA        | COLB
------------+--------
PLATE       | SPOON
OIL|JUG|MAT | JUG|MAT

我尝试过类似下面的方法以及很多方法，但没有成功。我对 SQL 部分了解不多:

SELECT *
FROM MYTABLE
WHERE COLA NOT LIKE '%COLB%'

Answer 1

一种方法是递归子查询:

with cte as (
      select convert(varchar(max), null) as parta,
             convert(varchar(max), cola) as resta,
             cola, colb,
             row_number() over (order by (select null)) as seqnum
      from t
      union all
      select convert(varchar(max),
                     left(resta, charindex('|', resta + '|') - 1)
                    ) as parta,
             convert(varchar(max),
                     stuff(resta, 1, charindex('|', resta + '|'), '')
                    ) as resta,
             cola, colb, seqnum
      from cte
      where resta <> ''
     )
select cola, colb
from cte
where parta is not null
group by seqnum, cola, colb
having sum(case when concat('|', colb, '|') like concat('%|', parta, '|%') then 1 else 0 end) <> count(*) or
       len(cola) <> len(colb);

Here是一个数据库<> fiddle 。

在支持字符串拆分和聚合的最新版本的 SQL Server 中，这要简单得多。