## where条件一个clob类型的字段like多次的问题

@IdleMan 你好，想跟你请教个问题：

```SELECT REGEXP_SUBSTR('man,peter,北京,25', '[^,]+', 1, LEVEL, 'i') item
from dual connect by rownum <= regexp_count('man,peter,北京,25',',') + 1```

man
peter

25

```select * from Table1  t1  where t1.beizhu like '%man%' and t1.beizhu like '%peter%'
and t1.beizhu like '%北京%' and t1.beizhu like '%25%'```

0

```WITH Table1 AS
( SELECT 'man,peter,北京,25' beizhu FROM dual
UNION
SELECT 'man2,peter,北京,25' beizhu FROM dual
UNION
SELECT 'peter,北京,25' beizhu FROM dual
)
SELECT *
FROM Table1 t1
WHERE EXISTS
(SELECT NULL
FROM
(SELECT REGEXP_SUBSTR('man,peter,北京,25', '[^,]+', 1, LEVEL, 'i') item,regexp_count('man,peter,北京,25',',') + 1 cnt
FROM dual
CONNECT BY rownum <= regexp_count('man,peter,北京,25',',') + 1
) t2
WHERE t1.beizhu LIKE '%'
|| t2.item
|| '%'
GROUP BY t1.beizhu
HAVING COUNT(*)=min(cnt)
);```

0

clob不支持group，可以group by t1.id

0

#### 引用来自“IdleMan”的评论

```WITH Table1 AS
( SELECT 'man,peter,北京,25' beizhu FROM dual
UNION
SELECT 'man2,peter,北京,25' beizhu FROM dual
UNION
SELECT 'peter,北京,25' beizhu FROM dual
)
SELECT *
FROM Table1 t1
WHERE EXISTS
(SELECT NULL
FROM
(SELECT REGEXP_SUBSTR('man,peter,北京,25', '[^,]+', 1, LEVEL, 'i') item,regexp_count('man,peter,北京,25',',') + 1 cnt
FROM dual
CONNECT BY rownum <= regexp_count('man,peter,北京,25',',') + 1
) t2
WHERE t1.beizhu LIKE '%'
|| t2.item
|| '%'
GROUP BY t1.beizhu
HAVING COUNT(*)=min(cnt)
);```

```WITH Table1 AS
( SELECT 'man,peter,北京,25' beizhu FROM dual
UNION
SELECT 'man2,peter,北京,25' beizhu FROM dual
UNION
SELECT 'kate,北京,25' beizhu FROM dual
)
SELECT *
FROM Table1 t1
WHERE EXISTS
(SELECT NULL
FROM
(SELECT REGEXP_SUBSTR('kate,man2', '[^,]+', 1, LEVEL, 'i') item,regexp_count('kate,man2',',') + 1 cnt
FROM dual
CONNECT BY rownum <= regexp_count('kate,man2',',') + 1
) t2
WHERE t1.beizhu LIKE '%'
|| t2.item
|| '%'
GROUP BY t1.beizhu
HAVING COUNT(*)=min(cnt)
);```

`man2,peter,北京,25`
`kate,北京,25`

>0