select REGEXP_SUBSTR(REGEXP_SUBSTR(str, '[^&]+', 1, LEVEL), '[^|]+', 1, 1) column1,
REGEXP_SUBSTR(REGEXP_SUBSTR(str, '[^&]+', 1, LEVEL), '[^|]+', 1, 2) column2
from (select 't1|t2&t3|t4' str from dual)
CONNECT BY LEVEL <= REGEXP_COUNT(str, '&') + 1
REGEXP_COUNT函数11G之后才有,之前的版本可以这样写
select REGEXP_SUBSTR(REGEXP_SUBSTR(str, '[^&]+', 1, LEVEL), '[^|]+', 1, 1) column1,
REGEXP_SUBSTR(REGEXP_SUBSTR(str, '[^&]+', 1, LEVEL), '[^|]+', 1, 2) column2
from (select 't1|t2&t3|t4' str from dual)
CONNECT BY LEVEL <= length(str)-length(replace(str, '&','')) + 1
REGEXP_SUBSTR(REGEXP_SUBSTR(str, '[^&]+', 1, LEVEL), '[^|]+', 1, 2) column2
from (select 't1|t2&t3|t4' str from dual)
CONNECT BY LEVEL <= REGEXP_COUNT(str, '&') + 1
REGEXP_COUNT函数11G之后才有,之前的版本可以这样写
select REGEXP_SUBSTR(REGEXP_SUBSTR(str, '[^&]+', 1, LEVEL), '[^|]+', 1, 1) column1,
REGEXP_SUBSTR(REGEXP_SUBSTR(str, '[^&]+', 1, LEVEL), '[^|]+', 1, 2) column2
from (select 't1|t2&t3|t4' str from dual)
CONNECT BY LEVEL <= length(str)-length(replace(str, '&','')) + 1
with a as (select replace(('t1|t2'|| '&' || 't3|t4'),'&',',') id from dual)
select regexp_substr(id,'[^,]+',1,rownum) id from a
connect by rownum<=length(regexp_replace(id,'[^,]+'))+1
regexp_substr(id,'[^,]+',1,rownum)--是按逗号分隔
regexp_substr(id,'[^&]+',1,rownum)--就是按&分隔
没必要非得转化为逗号,再进行分隔
regexp_substr(id,'[^,]+',1,rownum)--是按逗号分隔
regexp_substr(id,'[^&]+',1,rownum)--就是按&分隔
没必要非得转化为逗号,再进行分隔
你确定你这样OK,你试了没?这样会让你输入变量好吧!!!!
regexp_substr(id,'[^,]+',1,rownum)--是按逗号分隔
regexp_substr(id,'[^&]+',1,rownum)--就是按&分隔
没必要非得转化为逗号,再进行分隔
你确定你这样OK,你试了没?这样会让你输入变量好吧!!!!
&是在引号里面的,在引号里面不会进行转义的,你到sql环境执行下就知道了
column1,
2 REGEXP_SUBSTR(REGEXP_SUBSTR(str, '[^&]+', 1, LEVEL), '[^|]+', 1, 2) column2 3 from (select 't1|t2'||'&'||'t3|t4' str from dual)
4 CONNECT BY LEVEL <= REGEXP_COUNT(str, '&') + 1;COLUMN1 COLUMN2
---------------------- ----------------------
t1 t2
t3 t4SQL>
刚刚的测试结果,字符串中的&会被转义,'t1|t2'||'&'||'t3|t4' ,正则表达式中没啥问题