데이터베이스 질문과 답변
안녕하세요.
다음 문제 해결이 되지 않고 있습니다.
'A,B,C,,,D' 이런 문자열을
,(쉼표 구분자)로 분리하여 아래와 같이 세로로 보이고자 합니다.
seq value
1 A
2 B
3 C
4 null (값없음)
5 null (값없음)
6 D
도움 부탁 드립니다.
by 비가
[2011.06.23 14:47:43]
with t as
(
select 'A,B,C,,,D' str from dual
)
select level seq,trim(regexp_substr(replace(str,',',', '),'[^,]+',1,level)) str
from t
connect by level <= length(regexp_replace(str,'[^,]')) + 1
(
select 'A,B,C,,,D' str from dual
)
select level seq,trim(regexp_substr(replace(str,',',', '),'[^,]+',1,level)) str
from t
connect by level <= length(regexp_replace(str,'[^,]')) + 1
by 마농
[2011.06.23 14:56:52]
SELECT v
, level lv
, LTRIM(REGEXP_SUBSTR(v, '(^|,)[^,]*', 1, level), ',') v1
, TRIM(REGEXP_SUBSTR(REPLACE(v, ',', ', '), '[^,]+', 1, level)) v2
, SUBSTR(v, INSTR(','||v,',',1,level), INSTR(v||',',',',1,level) - INSTR(','||v,',',1,level)) v3
FROM (SELECT 'A,B,C,,,D' v FROM dual)
CONNECT BY level <= LENGTH(v) - LENGTH(REPLACE(v,',')) + 1
;
, level lv
, LTRIM(REGEXP_SUBSTR(v, '(^|,)[^,]*', 1, level), ',') v1
, TRIM(REGEXP_SUBSTR(REPLACE(v, ',', ', '), '[^,]+', 1, level)) v2
, SUBSTR(v, INSTR(','||v,',',1,level), INSTR(v||',',',',1,level) - INSTR(','||v,',',1,level)) v3
FROM (SELECT 'A,B,C,,,D' v FROM dual)
CONNECT BY level <= LENGTH(v) - LENGTH(REPLACE(v,',')) + 1
;
by 가우디
[2011.06.23 15:15:26]
아 감사합니다. 여러 방법들이 있었네요.
by 팅
[2011.06.24 09:22:54]
with t as
(
select 'A,B,C,,,D' str from dual
)
select level seq,trim(regexp_substr(replace(str,',',' ,'),'[^,]+',1,level)) str
from t
connect by level <= length(regexp_replace(str,'[^,]')) + 1
(
select 'A,B,C,,,D' str from dual
)
select level seq,trim(regexp_substr(replace(str,',',' ,'),'[^,]+',1,level)) str
from t
connect by level <= length(regexp_replace(str,'[^,]')) + 1
) 버튼을 클릭하여 작성 하시면 됩니다.