我有一个表 t1,我需要检查字符串 c1 是否存在于 c2 中 (需要 DB2 查询(
|C1 |C2 |
|------|--------|
|2 |235 |
|street|1 street|
|tomm |angel |
|25 |25 35 |
预期成果 :
C1 C2
街道 : 1 街道
25:25 35
按原样尝试以下操作。
这个想法是标记两列中的每个字符串,假设单词由一个或多个空格分隔。我们将两个单词列表连接在exists
谓词中以检查,如果两个单词中都有共同的单词。
with t(c1, c2) as
(
values
('2', '235')
, ('street', '1 street')
, ('tomm', 'angel')
, ('25', '25 35')
, ('winter season', 'summer season')
)
select c1, c2
from t
where exists
(
select 1
from xmltable
(
'for $id in tokenize($s, "s+") return <i>{string($id)}</i>' passing t.c1 as "s"
columns tok varchar(100) path '.'
) t1
join xmltable
(
'for $id in tokenize($s, "s+") return <i>{string($id)}</i>' passing t.c2 as "s"
columns tok varchar(100) path '.'
) t2 on t1.tok = t2.tok
);
结果是:
|C1 |C2 |
|---------------|---------------|
|street |1 street |
|25 |25 35 |
|winter season |summer season |