【大话IT】同一行两个字段匹配，已下划线分隔，精确匹配出结果

antipass · 发表于 2015-2-10 19:50

with t as(select  '92_93_94_96_98' as str,8 as id from dual union all
select  '92_93_94_96_98' as str,2 as id from dual union all
select  '92_93_94_96_98' as str,92 as id from dual union all
select  '92_93_94_96_98' as str,98 as id from dual union all
select  '92_93_94_96_98' as str,96 as id from dual union all
select  '92_93_94_96_98' as str,4 as id from dual union all
select  '92_93_94_96_98' as str,6 as id from dual union all
select  '92_93_94_96_98' as str,9 as id from dual union all
select  '92_93_94_96_98' as str,94 as id from dual union all
select  '92_93_94_96_98' as str,3 as id from dual union all
select  '92_93_94_96_98' as str,93 as id from dual)
select  * from  t
where regexp_like(str,id)

数据：

STR       ID
92_93_94_96_98       8
92_93_94_96_98       2
92_93_94_96_98       92
92_93_94_96_98       98
92_93_94_96_98       96
92_93_94_96_98       4
92_93_94_96_98       6
92_93_94_96_98       9
92_93_94_96_98       94
92_93_94_96_98       3
92_93_94_96_98       93

最终需求：已下划线分隔，精确匹配

STR       ID
92_93_94_96_98       92
92_93_94_96_98       98
92_93_94_96_98       96
92_93_94_96_98       94
92_93_94_96_98       93

谢谢！

zxy877298415 · 发表于 2015-2-10 21:08

with t as(select '92_93_94_96_98' as str,8 as id from dual union all
select '92_93_94_96_98' as str,2 as id from dual union all
select '92_93_94_96_98' as str,92 as id from dual union all
select '92_93_94_96_98' as str,98 as id from dual union all
select '92_93_94_96_98' as str,96 as id from dual union all
select '92_93_94_96_98' as str,4 as id from dual union all
select '92_93_94_96_98' as str,6 as id from dual union all
select '92_93_94_96_98' as str,9 as id from dual union all
select '92_93_94_96_98' as str,94 as id from dual union all
select '92_93_94_96_98' as str,3 as id from dual union all
select '92_93_94_96_98' as str,93 as id from dual)
select str, id
from (select id, str, regexp_substr(str, '[^_]+', 1, level) xxoo
from t
connect by level <= regexp_count(str,'_')+1
and prior id = id
and prior dbms_random.value() is not null)
where id = xxoo;

复制代码

YuBinTAMU · 发表于 2015-2-10 21:11

Here's a solution, though not good enough:
with t as(select  '92_93_94_96_98' as str,8 as id from dual union all
select  '92_93_94_96_98' as str,2 as id from dual union all
select  '92_93_94_96_98' as str,92 as id from dual union all
select  '92_93_94_96_98' as str,98 as id from dual union all
select  '92_93_94_96_98' as str,96 as id from dual union all
select  '92_93_94_96_98' as str,4 as id from dual union all
select  '92_93_94_96_98' as str,6 as id from dual union all
select  '92_93_94_96_98' as str,9 as id from dual union all
select  '92_93_94_96_98' as str,94 as id from dual union all
select  '92_93_94_96_98' as str,3 as id from dual union all
select  '92_93_94_96_98' as str,93 as id from dual)
select  str, id from  t
where regexp_like('_'||str||'_','_'||id||'_');

jboracle1981 · 发表于 2015-2-10 21:12

instr('-'||str||'-','-'||id||'-') > 0

jboracle1981 · 发表于 2015-2-10 21:18

YuBinTAMU 发表于 2015-2-10 21:11
Here's a solution, though not good enough:
with t as(select '92_93_94_96_98' as str,8 as id fro ...

兄台，已打入邪恶的美利坚？

YuBinTAMU · 发表于 2015-2-10 21:19

Ok, the better solution I thought:
with t as(select  '92_93_94_96_98' as str,8 as id from dual union all
select  '92_93_94_96_98' as str,2 as id from dual union all
select  '92_93_94_96_98' as str,92 as id from dual union all
select  '92_93_94_96_98' as str,98 as id from dual union all
select  '92_93_94_96_98' as str,96 as id from dual union all
select  '92_93_94_96_98' as str,4 as id from dual union all
select  '92_93_94_96_98' as str,6 as id from dual union all
select  '92_93_94_96_98' as str,9 as id from dual union all
select  '92_93_94_96_98' as str,94 as id from dual union all
select  '92_93_94_96_98' as str,3 as id from dual union all
select  '92_93_94_96_98' as str,93 as id from dual)
select  str, id from  t
where regexp_like(str,'(^|_)'||id||'(_|$)');

Thanks to the post that have me learnt more about regexp