by 이누야샤 [SQL Query] REGEXP_SUBSTR 구분자'###' [2016.09.03 22:51:46]
with a as
(
select '1###2###3#4###5' as a from dual
)
select regexp_substr(a,'[^#(3)]+',1,1) as a_1
,regexp_substr(a,'[^#(3)]+',1,2) as a_2
,regexp_substr(a,'[^#(3)]+',1,3) as a_3
,regexp_substr(a,'[^#(3)]+',1,4) as a_4
,regexp_substr(a,'[^#(3)]+',1,5) as a_5
,regexp_count(a,'[^#(3)]') as a_c
from a ;
결과
a_1 a_2 a_3 a_4 a_5 a_c
1 2 4 5 4
원하는 결과
a_1 a_2 a_3 a_4 a_5 a_c
1 2 3#4 5 4
a_3 에 '3#4' 값이 나왔으면 합니다.