[SQL] 오라클 Oracle 문자열 자르는 SUBSTR 함수 사용법

SUBSTR 함수는 오라클 데이터베이스에서 제공하는 함수로 문자열을 자릿수로 자르는 함수다. SUBSTR 함수의 사용 방법과 실무에서 어떤 방식으로 많이 사용하는지 알아보자. 

1. SUBSTR 함수 사용 방법

SUBSTR("문자열", "시작위치")
SUBSTR("문자열", "시작위치", "길이")

SUBSTR 함수 인자값으로는 첫 번째 "문자열", 두 번째 "시작위치", 세 번째는 "길이"다.
문자열에 들어갈 수 있는 데이터 타입은
CHAR, VARCHAR2, NCHAR, NVARCHER2, CLOB, NCLOB 형이다.
시작위치와 길이는 데이터 타입이 NUMBER 형만 사용 가능하다. (숫자로 변환되는 문자열도 가능)

반응형

SUBSTR("문자열", "시작위치")

SELECT SUBSTR('반갑습니다.1234', 3) AS TXT FROM DUAL;
--결과 : 습니다.1234

SELECT SUBSTR('반갑습니다.1234', -4) AS TXT FROM DUAL;
--결과 : 1234

시작위치가 양수이면 앞에서, 음수(-) 면 뒤에서부터 시작위치를 찾는다. 시작위치에서 문자열의 끝자리까지 잘라서 가져온다.

SUBSTR("문자열", "시작위치", "길이")

SELECT SUBSTR('반갑습니다.1234', 3, 5) AS TXT FROM DUAL;
--결과 : 습니다.1

SELECT SUBSTR('반갑습니다.1234', -4, 2) AS TXT FROM DUAL;
--결과 : 12

시작위치로부터 길이만큼만 잘라낸다.

만약 시작위치가 문자열 범위를 벗어나거나 길이가 0 또는 음수일 경우에는 NULL을 return 한다.

SELECT SUBSTR('반갑습니다.1234', 3, 0) AS TXT FROM DUAL;
--결과 : [NULL]

SELECT SUBSTR('반갑습니다.1234', 50, 3) AS TXT FROM DUAL;
--결과 : [NULL]

SELECT SUBSTR('반갑습니다.1234', 4, -3) AS TXT FROM DUAL;
--결과 : [NULL]

2. SUBSTR 함수 활용

실무에서 SUBSTR 함수는 WHERE 조건절에서 많이 사용한다. LIKE 조건을 사용하면 의도치 않은 데이터가 조회되는 경우가 있기 때문에 SUBSTR 함수를 사용해 명확하게 조건을 주는 경우가 많다.

emp 테이블 데이터

JOB 컬럼의 값 중에서 3번째 자리 값이 1인 경우를 조회할 경우 아래와 같이 SUBSTR 함수를 이용해 WHERE 절을 만들 수 있다.

SELECT * FROM EMP WHERE SUBSTR(JOB, 3, 1) = 1;

조회 결과

그리고 ENAME 칼럼의 값 중에서 2번째 자리 값이 '아'인 경우를 조회할 경우에도 SUBSTR 함수를 이용해 WHERE 절을 만들면 된다.

SELECT * FROM EMP WHERE SUBSTR(ENAME, 2, 1) = '아';

조회 결과

SUBSTR 함수와 같은 기능이지만 데이터 형식에 따라 SUBSTRB, SUBSTRC, SUBSTR2, SUBSTR4 함수가 있다.

SUBSTRB - 문자열이 아닌 바이트 형식일 경우 사용
SUBSTRC - 유니코드 문자 형식일 경우 사용
SUBSTR2 - UCS2 코드단위 형식일 경우 사용
SUBSTR4 - UCS4 코드포인트 형식일 경우 사용

데이터 형식에 맞는 함수를 사용해 데이터를 자를 수 있다.

SQL 관련 포스팅

Oracle SQL 계층형 쿼리 Connect By Prior 사용하기

Oracle SQL 계층형 쿼리 Connect By Prior 사용하기