每个字符串包含2个$符号
依据$将相应位置的字符拆分成3个字段
字段1为字符串首位到第一个$间的字符
字段2为第一个$到第二个$间的字符
字段3为第二个$到字符串结尾的字符
若相应位置没有字符,则字段为空用regexp_substr和case when 基本能实现
但既然用了正则,想尽量少用case when
求用正则简化的脚本实现with t as
(
select 'aa$bbb$cccc' a from dual union all
select 'aa$bbb$' from dual union all
select 'aa$$cccc' from dual union all
select '$bbb$cccc' from dual union all
select 'aa$$' from dual union all
select '$bbb$' from dual union all
select '$$cccc' from dual union all
select '$$' from dual
)
select a
from t
;--结果目标
a1 a2 a3
aa bbb cccc
aa bbb
aa cccc
bbb cccc
aa
bbb
cccc
依据$将相应位置的字符拆分成3个字段
字段1为字符串首位到第一个$间的字符
字段2为第一个$到第二个$间的字符
字段3为第二个$到字符串结尾的字符
若相应位置没有字符,则字段为空用regexp_substr和case when 基本能实现
但既然用了正则,想尽量少用case when
求用正则简化的脚本实现with t as
(
select 'aa$bbb$cccc' a from dual union all
select 'aa$bbb$' from dual union all
select 'aa$$cccc' from dual union all
select '$bbb$cccc' from dual union all
select 'aa$$' from dual union all
select '$bbb$' from dual union all
select '$$cccc' from dual union all
select '$$' from dual
)
select a
from t
;--结果目标
a1 a2 a3
aa bbb cccc
aa bbb
aa cccc
bbb cccc
aa
bbb
cccc
(
select 'aa$bbb$cccc' a from dual union all
select 'aa$bbb$' from dual union all
select 'aa$$cccc' from dual union all
select '$bbb$cccc' from dual union all
select 'aa$$' from dual union all
select '$bbb$' from dual union all
select '$$cccc' from dual union all
select '$$' from dual
)
select regexp_substr(a,'[^$]+',1,1),
regexp_substr(a,'[^$]+',2,2),
regexp_substr(a,'[^$]+',2,3)
from t ;--这样好像有点小问题,lz在调试下吧
select '$$cccc' from dual ;
这句的结果应该是NULL NULL cccc,现在出来的是:cccc NULL NULL
(
select 'aa$bbb$cccc' a from dual union all
select 'aa$bbb$' from dual union all
select 'aa$$cccc' from dual union all
select '$bbb$cccc' from dual union all
select 'aa$$' from dual union all
select '$bbb$' from dual union all
select '$$cccc' from dual union all
select '$$' from dual
)
select a,
substr(a, 1, instr(a, '$', 1, 1)-1) b
,substr(a, instr(a, '$', 1, 1)+1, instr(a, '$', 1, 2)-instr(a, '$', 1, 1)-1) c
,substr(a, instr(a, '$', 1, 2)+1) d
from t;
现在就是想把substr和instr转为只用regexp_substr实现
尽量少用case when我相信正则的威力,应该有更通用和严谨的写法
[SYS@orcl] SQL>col a1 format a5
[SYS@orcl] SQL>col a2 format a5
[SYS@orcl] SQL>col a3 format a5
[SYS@orcl] SQL>with t as(
2 select 'aa$bbb$cccc' a from dual union all
3 select 'aa$bbb$' from dual union all
4 select 'aa$$cccc' from dual union all
5 select '$bbb$cccc' from dual union all
6 select 'aa$$' from dual union all
7 select '$bbb$' from dual union all
8 select '$$cccc' from dual union all
9 select '$$' from dual
10 ),t2 as(SELECT regexp_replace(a,'([$])',' \1 ') a FROM t
11 )SELECT TRIM(regexp_substr(a,'[^$]+',1,1)) a1,
12 TRIM(regexp_substr(a,'[^$]+',1,2)) a3,
13 TRIM(regexp_substr(a,'[^$]+',1,3)) a2
14 FROM t2;A1 A3 A2
----- ----- -----
aa bbb cccc
aa bbb
aa cccc
bbb cccc
aa
bbb
cccc
已选择8行。
再来化简
with t as
(
select 'aa$bbb$cccc' a from dual union all
select 'aa$bbb$' from dual union all
select 'aa$$cccc' from dual union all
select '$bbb$cccc' from dual union all
select 'aa$$' from dual union all
select '$bbb$' from dual union all
select '$$cccc' from dual union all
select '$$' from dual
)
select a,
rtrim(regexp_substr(a||'$', '[^\$]*\$'),'$') b,
rtrim(regexp_substr(a||'$', '[^\$]*\$', 1,2),'$') c,
rtrim(regexp_substr(a||'$', '[^\$]*\$',1,3),'$') d
from t;
[SYS@orcl] SQL>set null /
[SYS@orcl] SQL>with t as
2 (
3 select 'aa$bbb$cccc' a from dual union all
4 select 'aa$bbb$' from dual union all
5 select 'aa$$cccc' from dual union all
6 select '$bbb$cccc' from dual union all
7 select 'aa$$' from dual union all
8 select '$bbb$' from dual union all
9 select '$$cccc' from dual union all
10 select '$$' from dual
11 )
12 select a,
13 regexp_substr(a, '[^$]*',1,1)a1,
14 regexp_substr(a, '[^$]*',1,2)a2,
15 regexp_substr(a, '[^$]*',1,3)a3
16 from t;A A1 A2 A3
----------- ----- ----- -----
aa$bbb$cccc aa / bbb
aa$bbb$ aa / bbb
aa$$cccc aa / /
$bbb$cccc / bbb /
aa$$ aa / /
$bbb$ / bbb /
$$cccc / / cccc
$$ / / /已选择8行。
你想要的是这样的结果?with t as
(select 'aa$bbb$cccc' a from dual union all
select 'aa$bbb$' from dual union all
select 'aa$$cccc' from dual union all
select '$bbb$cccc' from dual union all
select 'aa$$' from dual union all
select '$bbb$' from dual union all
select '$$cccc' from dual union all
select '$$' from dual)
Select a,
Regexp_Substr(a, '[^$]+', 1, 1) A1,
Regexp_Substr(a, '[^$]+', 1, 2) A2,
Regexp_Substr(a, '[^$]+', 1, 3) A3
From t;
with t as
(
select 'aa$$cccc' a from dual union all
select 'aa$bbb$cccc' a from dual union all
select 'aa$bbb$' from dual union all
select 'aa$$cccc' from dual union all
select '$bbb$cccc' from dual union all
select 'aa$$' from dual union all
select '$bbb$' from dual union all
select '$$cccc' from dual union all
select '$$' from dual
)
select '$'||a,
ltrim(Regexp_Substr('$'||a, '\$[^$]*', 1, 1),'$') A1,
ltrim(Regexp_Substr('$'||a, '\$[^$]*', 1, 2),'$') A2,
ltrim(Regexp_Substr('$'||a, '\$[^$]*', 1, 3),'$') A3from t ;
由于oracle的正则表达式不支持去除匹配内容的元字符,所以需要trim来去除分隔符。
with t as(
select 'aa$bbb$cccc' a from dual union all
select 'aa$bbb$' from dual union all
select 'aa$$cccc' from dual union all
select '$bbb$cccc' from dual union all
select 'aa$$' from dual union all
select '$bbb$' from dual union all
select '$$cccc' from dual union all
select '$$' from dual
)
SELECT a
,regexp_substr(a,'\w*') a1
,regexp_replace(regexp_substr(a,'[$]\w*',1,1),'[$]') a2
,regexp_replace(regexp_substr(a,'[$]\w*',1,2),'[$]') a3
FROM t;
才发现oracle的正则不支持零宽断言,否则一个regexp_substr就可以搞定了