[ORACLE] 문자열 관련 함수 정리 (2) - SUBSTR, INSTR, REPLACE, CONCAT

 

문자열 자르기

SUBSTR

• SUBSTR(문자열, 시작 위치, 추출 길이) : 문자열의 시작 위치부터 추출 길이만큼의 문자 데이터를 반환하는 함수

시작 위치가 음수일 경우 마지막 위치부터 거슬러 올라간 위치에서 시작한다.
추출 길이를 생략하여 SUBSTR(문자열, 시작 위치)로 사용하면, 시작 위치부터 문자열 끝까지 반환한다.

SELECT '대한민국', 
      SUBSTR('대한민국', 1, 2), 
      SUBSTR('대한민국', -1, 2), 
      SUBSTR('대한민국', 3) 
FROM DUAL;

1. SUBSTR('대한민국', 1, 2) : 첫 번째 글자부터 두 글자 출력 → 대한
2. SUBSTR('대한민국', -1, 2) : 시작 위치가 음수이기 때문에, 뒤에서 첫 번째 글자부터 두 글자 출력 → 국 (더 이상 문자가 없기에 '국'만 출력)
3. SUBSTR('대한민국', 3) : 세 번째 글자부터 끝까지 출력 → 민국

✍ 사용 팁!

주민등록번호 중 생년월일 앞자리만 추출 시 사용 전화번호의 마지막 네 자리만 추출 시 사용

💡 참고!

SUBSTR 함수와 LENGTH 함수의 사용 (다른 함수와 사용하기)
시작 위치나 추출 길이의 숫자 값에 LENGTH 함수로 추출한 값을 넣어 사용할 수 있다.

SELECT '재미있는 오라클', 
	SUBSTR('재미있는 오라클', -LENGTH('재미있는 오라클'), 2) 
FROM DUAL;

 

특정 문자 위치 값 찾기

INSTR

• INSTR(문자열, 찾을 문자, 시작 위치, 찾을 순번) : 문자열의 시작 위치부터 찾으려는 문자의 위치를 반환하는 함수

문자열과 찾을 문자는 필수 입력 값이다.
시작 위치는 위치를 찾을 문자열의 시작 데이터 위치, 찾을 순번은 찾으려는 문자가 시작 위치에서 부터 몇 번째인지 지정한다. 시작 위치가 음수일 경우 문자열의 오른쪽 끝 부터 왼쪽 방향으로 검색한다.

SELECT 'HELLO ORACLE', 
        INSTR('HELLO ORACLE', 'L'), 
        INSTR('HELLO ORACLE', 'L', 5), 
        INSTR('HELLO ORACLE', 'L', 2, 2), 
        INSTR('HELLO ORACLE', 'L', -3, 2) 
FROM DUAL;

1. INSTR('HELLO ORACLE', 'L') : 문자열에서 처음부터 검색해 'L'의 위치 반환 → 3 (HELLO ORACLE)
2. INSTR('HELLO ORACLE', 'L', 5) : 문자열에서 다섯 번째 글자 'O'부터 검색해 'L'의 위치 반환 → 11 (HELLO ORACLE)
3. INSTR('HELLO ORACLE', 'L', 2, 2) : 문자열에서 두 번째 글자 'E'부터 검색해 두 번째에 있는 'L'의 위치 반환 → 4 (HELLO ORACLE)
4. INSTR('HELLO ORACLE', 'L', -3, 2) : 문자열을 뒤에서부터 세 번째 글자 'C'부터 앞쪽으로 검색해 두 번째에 있는 'L'의 위치 반환 → 3 (HELLO ORACLE)

✍ 사용 팁!

찾는 문자가 없을 경우 0을 반환하기 때문에 LIKE 연산자처럼 사용하기도 한다.

SELECT * 
FROM EMP 
WHERE INSTR(ENAME, 'S') > 0; // 0보다 크면 결과가 존재한다는 뜻

💡 참고!

특정 문자를 기준으로 문자열을 자르기 SUBSTR과 INSTR을 함께 사용한다.

SELECT SUBSTR('HELLO, ORACLE', 0, INSTR('HELLO, ORACLE', ',')-1) 
FROM DUAL; // 쉼표를 기준으로 자르기

 

특정 문자 바꾸기

REPLACE

• REPLACE(문자열, 찾는 문자, 바꿀 문자) : 문자열에 포함된 특정 문자를 다른 문자로 대체하는 함수

바꿀 문자를 입력하지 않으면 찾는 문자가 문자열에서 삭제된다.

SELECT REPLACE('HELLO','L','O'), 
	REPLACE('HELLO','HE') 
FROM DUAL;

 

두 문자열 합치기

CONCAT

• CONCAT(문자열1, 문자열2) : 두 문자열을 하나의 데이터로 연결하는 함수

SELECT CONCAT('KOREA','만세'),
	CONCAT('KOREA',CONCAT('!!','만세')) 
FROM DUAL;

💡 참고!

문자열을 연결하는 || 연산자도 CONCAT 함수와 유사하게 사용