假设表A,
id | pos |
1 | 黑龙江,湖北 |
2 | 江西,黑龙江 |
数据库中pos中包含黑龙江的匹配查询:
select * from (
select tt.*, regexp_substr(pos, '[^,]+', 1, level) position
from A tt
connect by level <= regexp_count(pos, ',') + 1
and id= prior id
and prior dbms_random.value is not null
)
where position=‘黑龙江’
结果:
id | pos | position |
1 | 黑龙江,湖北 | 黑龙江 |
2 | 江西,黑龙江 | 黑龙江 |