데이터베이스 질문과 답변
SELECT
TEST
,'컬럼1' AS TEST1
,'컬럼2' AS TEST2
FROM (
SELECT
'01|0001' AS TEST
FROM DUAL
UNION ALL
SELECT
'002|0002' AS TEST
FROM DUAL
);
TEST 컬럼을 "|" 구분자를 기준으로 두개의 컬럼(TEST1,TEST2)으로 나눠서 표현하려고 하면 어떻게 해야 할까요?
by 우리집아찌
[2018.01.29 13:27:01]
WITH T AS (
SELECT '01|0001' AS TEST FROM DUAL
UNION ALL
SELECT '002|0002' AS TEST FROM DUAL
)
SELECT REGEXP_SUBSTR(TEST,'[^|]+',1,1) COL1
, REGEXP_SUBSTR(TEST,'[^|]+',1,2) COL2
FROM T
by 마농
[2018.01.30 09:01:27]
SELECT SUBSTR(test, 1, INSTR(test, '|') - 1) col1
, SUBSTR(test, INSTR(test, '|') + 1) col2
FROM t
;
) 버튼을 클릭하여 작성 하시면 됩니다.