오라클 컬럼 분리 - olakeul keolleom bunli

Question

IT/기타

Oracle SQL 특정 문자 기준으로 문자열 분리하기(행으로 분리)

twofootdog 2021. 5. 26. 19:44

Oracle SQL을 작성하다 보면 특정 문자를 기준으로 문자열을 행(row)으로 분리해야 하는 경우가 종종 있다.

만약 테이블 컬럼의 데이터가 "10|20|30|40|50|" 인 경우, 문자 "|"를 기준으로 10~50으로 데이터를 분리(5개의 row) 하려면 아래와 같이 SQL을 작성하면 된다(아래 SQL에서는 테이블이 WITH절, 컬럼은 STR이고, 분리된 ROW 명이 CODE 다 )

문자열

SQL

WITH TMP AS (SELECT '10|20|30|40|50|' AS STR FROM DUAL)
SELECT
	REGEXP_SUBSTR(TMP.STR, '[^|]+', 1, LEVEL) CODE
FROM
	DUAL, TMP
CONNECT BY	REGEXP_SUBSTR(TMP.STR, '[^|]+', 1, LEVEL) IS NOT NULL;

SQL 수행 결과

전체/Oracle

오라클 column to row (컬럼의 데이터를 여러개의 row로 가져오기)

effortDev 2020. 2. 26. 14:15

- 컬럼을 여러개의 로우로 표현하기

테이블의 데이터를 보면 한 컬럼안의 구분자를 사용하여 여러개의 데이터를 저장하여 사용하는 테이블이 있다.

SELECT '02,031,032' LIST_COL FROM DUAL

cs

│ LIST_COL │

│───────────────│

│ 02,031,032 │

└───────────────┘

cs

현재 LIST_COL 컬럼에는 한 컬럼안에 데이터가 ,(콤마)로 구분되어 코드값이 저장되어 있다.

1개의 로우로 표현된 컬럼 값을 ,(콤마)로 데이터를 분리하여 여러개의 ROW로 표현해보겠다.

1. , (콤마)로 구분되어 코드값을 여러개의 ROW로 추출하기

WITH LIST_TXT AS (SELECT '02,031,032' LIST_COL FROM DUAL)

SELECT TRIM(REGEXP_SUBSTR(LIST_COL,'[^,]+',1,LEVEL)) AS LIST_TO_ROW

FROM LIST_TXT

CONNECT BY INSTR(LIST_COL, ',' , 1, LEVEL - 1 ) > 0

cs

SELECT TRIM(REGEXP_SUBSTR('02,031,032','[^,]+',1,LEVEL)) AS LIST_TO_ROW

FROM DUAL

CONNECT BY INSTR('02,031,032', ',', 1, LEVEL-1 ) > 0

cs

[결과]

│ LIST_TO_ROW │

│───────────────│

│ 02 │

├───────────────┤

│ 031 │

├───────────────┤

│ 032 │

└───────────────┘

cs

이렇게 사용하면 여러개의 테이블에 필요한 데이터를 추출하고 싶을때 SUBQUERY를 사용하여 뽑아낼수 있다.

2. 응용하기 ( 데이터 리스트를 row로 표현하여 해당 데이터만 뽑아내기 )

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

SELECT A.REGION

, A.AREA_CODE

, B.RESIDENCE_NAME

FROM

(

SELECT '000' AS P_CODE,'서울' AS REGION, '02' AREA_CODE FROM DUAL

UNION ALL

SELECT '000' AS P_CODE, '경기' AS REGION, '031' AREA_CODE FROM DUAL

UNION ALL

SELECT '000' AS P_CODE, '인천' AS REGION, '032' AREA_CODE FROM DUAL

UNION ALL

SELECT '000' AS P_CODE, '강원' AS REGION, '033' AREA_CODE FROM DUAL

UNION ALL

SELECT '000' AS P_CODE, '부산' AS REGION, '051' AREA_CODE FROM DUAL

) A

INNER JOIN

(

SELECT '000' AS P_CODE, '조현영' AS RESIDENCE_NAME, '02,031,032' AS AREA_CODE_LIST FROM DUAL

) B

ON A.P_CODE = B.P_CODE

WHERE A.AREA_CODE IN(

SELECT TRIM(REGEXP_SUBSTR(B.AREA_CODE_LIST,'[^,]+',1,LEVEL)) AS LIST_TO_ROW

FROM DUAL

CONNECT BY INSTR(B.AREA_CODE_LIST, ',', 1, LEVEL-1 ) > 0

)

cs

임시로 A, B 테이블 데이터를 정의하고

A테이블에는 지역과 지역번호 데이터가 있고

B테이블에는 지역번호 데이터가 ,(콤마)로 구분되어 여러개 들어가 있다.

[A 테이블]

P_CODE REGION AREA_CODE

000 │ 서울 │ 02 │

000 │ 경기 │ 031 │

000 │ 인천 │ 032 │

000 │ 강원 │ 033 │

000 │ 부산 │ 051 │

cs

[B 테이블]

P_CODE RESIDENCE_NAME AREA_CODE_LIST

000 │ 조현영 │ 02,031,032 │

cs

A테이블과 B테이블을 조인하여

조현영의 지역번호에 해당하는 지역명을 추출하려면 REGEXP_SUBSTR 정규식을 사용해

한 컬럼에 저장된 리스트를 여러개의 row로 변환하여

in 조건절 안에 해당하는 데이터만 추출할 수 있는 것을 확인할 수 있다.

[결과]

REGION AREA_CODE RESIDENCE_NAME

서울 │ 02 │ 조현영 │

경기 │ 031 │ 조현영 │

인천 │ 032 │ 조현영 │

cs

오라클 콤마 분리 오라클 split 가로 오라클 split 오라클 문자 오라클 regexp_substr Tibero split 함수 오라클 구분자 자르기

오라클 컬럼 분리 - olakeul keolleom bunli

Oracle SQL 특정 문자 기준으로 문자열 분리하기(행으로 분리)

오라클 column to row (컬럼의 데이터를 여러개의 row로 가져오기)

관련 게시물

오라클 UPDATE 속도 개선 - olakeul UPDATE sogdo gaeseon

식용유 병 분리 수거 - sig-yong-yu byeong bunli sugeo

SQL 문제 예제 - SQL munje yeje

오라클 PK 인덱스 삭제 - olakeul PK indegseu sagje

Input 숫자만 입력 정규식 - input susjaman iblyeog jeong-gyusig

오라클 setup - olakeul setup

ORACLE_SID 추가 - ORACLE_SID chuga

오라클 ROW 갯수 - olakeul ROW gaes-su

스투 키 분갈이 - seutu ki bungal-i

오라클 탭 공백 제거 - olakeul taeb gongbaeg jegeo

광고하는

최근 소식

신한카드 캐시백 이벤트 - sinhankadeu kaesibaeg ibenteu

심술 소주 나무위키 - simsul soju namuwiki

엑셀 원형 차트 정렬 - egsel wonhyeong chateu jeonglyeol

문명6 중국 종교관 - munmyeong6 jung-gug jong-gyogwan

웹툰 웹소설 무료 사이트 - webtun websoseol mulyo saiteu

윈도우11 잠금화면 사진 다운로드 - windou11 jamgeumhwamyeon sajin daunlodeu

Ms워드 자음 모음 분리 - mswodeu ja-eum mo-eum bunli

회사 포트폴리오 사이트 - hoesa poteupollio saiteu

38 사 기동대 9화 다시보기 - 38 sa gidongdae 9hwa dasibogi

해외 펀딩 사이트 - haeoe peonding saiteu

광고하는

포퓰러

광고하는

에 대한

합법적인

돕다

사회의