你可以把一行字符串以逗号为分隔符转换成多行,然后查询被拆分的字符串等于传入参数的记录如果有记录则包含,如果没有则不包含。脚本如下: select c from (with test as (select '1,5,131,171' c from dual)--引号内为目标字符串 select regexp_substr(t.ca, '[^,]+', 1, lv) AS c from (select c AS ca, length(c || ',') - nvl(length(REPLACE(c, ',')), 0) AS cnt FROM test) t, (select LEVEL lv from dual CONNECT BY LEVEL <= 100) c where c.lv <= t.cnt) where c = 1--'1'为参数
select 1 from dual where ','||'121,171,12'||',' like '%,'||'121'||',%' ;select 1 from dual where ','||ids||',' like '%,'||a|',%'
select 1 from dual where instr( ','||'121,171,12'||',' ,',121')>0;
谢谢解答 试了一下 好像要改一下· select 1 from dual where ids like '121'||',%' or ids like '%,'||'121' ; ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ',121,' 匹配不了 '121,122,123'
select 1 from dual where ','||'121,171,12'||',' like '%,'||'121'||',%' ;select 1 from dual where instr( ','||'121,171,12'||',' ,',121')>0; 没错 我看错了 谢谢 结贴
如:ids="121,171," a=12
函数 instr(ids,'a')>0,结果包含了12
"121,171" 里面2个数字121 171 是没有12的
若是 "12,121,171"才应该包含12,
清楚?
select c
from (with test as (select '1,5,131,171' c from dual)--引号内为目标字符串
select regexp_substr(t.ca, '[^,]+', 1, lv) AS c
from (select c AS ca,
length(c || ',') - nvl(length(REPLACE(c, ',')), 0) AS cnt
FROM test) t,
(select LEVEL lv from dual CONNECT BY LEVEL <= 100) c
where c.lv <= t.cnt)
where c = 1--'1'为参数
select 1 from dual where ','||'121,171,12'||',' like '%,'||'121'||',%' ;select 1 from dual where ','||ids||',' like '%,'||a|',%'
select 1 from dual where instr( ','||'121,171,12'||',' ,',121')>0;
试了一下 好像要改一下·
select 1 from dual where ids like '121'||',%' or ids like '%,'||'121' ;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
',121,' 匹配不了 '121,122,123'
没错 我看错了
谢谢 结贴