데이터베이스 질문과 답변
한 컬럼에 복수값을 분리하여 조회하려면...
0
2
2,857
by 한정윤 [2011.06.20 20:57:08]
안녕하세요...
데이터가 아래와 같이
A B C
데이터가 아래와 같이
A B C
409666 649027 T-54440:T-54430:T-54450:T-54460
409666 649025 T-54260
409666 649028 T-54390:T-54380:T-54370
412997 665489 T-54320:T-54330
412997 665490 T-54280:T-54270:T-54290:T-54300
있습니다.
C 컬럼에 " : "을 기준으로 여러 값(복수값)이 들어있는데,
저것을 하나씩 뽑아내려고 합니다..
고심을 해봐도 선뜻 해결책이 떠오르지 않아서 여기에 질문 올려봅니다..
아시는분 계시면 답변 달아주시면 정말 감사하겠습니다...^^
있습니다.
C 컬럼에 " : "을 기준으로 여러 값(복수값)이 들어있는데,
저것을 하나씩 뽑아내려고 합니다..
고심을 해봐도 선뜻 해결책이 떠오르지 않아서 여기에 질문 올려봅니다..
아시는분 계시면 답변 달아주시면 정말 감사하겠습니다...^^
by 마농
[2011.06.21 08:32:32]
WITH t AS
(
SELECT 409666 a, 649027 b, 'T-54440:T-54430:T-54450:T-54460' c FROM dual
UNION ALL SELECT 409666, 649025, 'T-54260' FROM dual
UNION ALL SELECT 409666, 649028, 'T-54390:T-54380:T-54370' FROM dual
UNION ALL SELECT 412997, 665489, 'T-54320:T-54330' FROM dual
UNION ALL SELECT 412997, 665490, 'T-54280:T-54270:T-54290:T-54300' FROM dual
)
SELECT a, b
, lv
, REGEXP_SUBSTR(c, '[^:]+', 1, lv) d
FROM t
, (SELECT level lv FROM dual CONNECT BY level <= 9)
WHERE lv <= LENGTH(c) - LENGTH(REPLACE(c, ':')) + 1
ORDER BY a, b, lv
;
(
SELECT 409666 a, 649027 b, 'T-54440:T-54430:T-54450:T-54460' c FROM dual
UNION ALL SELECT 409666, 649025, 'T-54260' FROM dual
UNION ALL SELECT 409666, 649028, 'T-54390:T-54380:T-54370' FROM dual
UNION ALL SELECT 412997, 665489, 'T-54320:T-54330' FROM dual
UNION ALL SELECT 412997, 665490, 'T-54280:T-54270:T-54290:T-54300' FROM dual
)
SELECT a, b
, lv
, REGEXP_SUBSTR(c, '[^:]+', 1, lv) d
FROM t
, (SELECT level lv FROM dual CONNECT BY level <= 9)
WHERE lv <= LENGTH(c) - LENGTH(REPLACE(c, ':')) + 1
ORDER BY a, b, lv
;
by 허재영
[2011.06.21 14:44:22]
WITH t AS
(
SELECT 409666 a, 649027 b, 'T-54440:T-54430:T-54450:T-54460' c FROM dual
UNION ALL SELECT 409666, 649025, 'T-54260' FROM dual
UNION ALL SELECT 409666, 649028, 'T-54390:T-54380:T-54370' FROM dual
UNION ALL SELECT 412997, 665489, 'T-54320:T-54330' FROM dual
UNION ALL SELECT 412997, 665490, 'T-54280:T-54270:T-54290:T-54300' FROM dual
)
SELECT a, b
, REGEXP_SUBSTR(c, '[^:]+', 1, level) d
FROM t
CONNECT BY CONNECT_BY_ROOT c = c
and level <= LENGTH(c) - LENGTH(REPLACE(c, ':')) + 1
(
SELECT 409666 a, 649027 b, 'T-54440:T-54430:T-54450:T-54460' c FROM dual
UNION ALL SELECT 409666, 649025, 'T-54260' FROM dual
UNION ALL SELECT 409666, 649028, 'T-54390:T-54380:T-54370' FROM dual
UNION ALL SELECT 412997, 665489, 'T-54320:T-54330' FROM dual
UNION ALL SELECT 412997, 665490, 'T-54280:T-54270:T-54290:T-54300' FROM dual
)
SELECT a, b
, REGEXP_SUBSTR(c, '[^:]+', 1, level) d
FROM t
CONNECT BY CONNECT_BY_ROOT c = c
and level <= LENGTH(c) - LENGTH(REPLACE(c, ':')) + 1
) 버튼을 클릭하여 작성 하시면 됩니다.