[SQL] SUBSTR / INSTR / CONCAT / TRIM / LPAD&RPAD / REPLACE (한 눈에 보는 문자열 함수 모음집)

 

언제든지 한 눈에 볼 수 있는 문자열을 다루는 SQL 기본 함수 모음집.

1. SUBSTR(컬럼, 시작위치, 문자수) : 컬럼에서 필요한만큼 문자수를 잘라서 가져옴

SELECT SUBSTR('쿼리로 문자열 다루기' , 5, 3) FROM DUAL
> 문자열

SELECT SUBSTR('쿼리로 문자열 다루기' , 5) FROM DUAL
> 문자열 다루기

SELECT SUBSTR('쿼리로 문자열 다루기' , -3, 2) FROM DUAL
> 다루

정규표현식도 사용이 가능하다.

 

SELECT REGEXP_SUBSTR('문자열', '찾을내용 정규식', 시작위치, 몇번째) FROM DUAL;

 

예를 들어 컬럼가장 처음부터 IP주소를 찾아서 가장 먼제나오는 내용을 리턴하는 경우

SELECT REGEXP_SUBSTR(COL1, '[1-9]*[0-9]*[0-9]*\.[0-9]*[0-9]*[0-9]*\.[0-9]*[0-9]*[0-9]*\.[0-9]*[0-9]*[0-9]*', 1, 1) 
FROM DUAL;

0~9의 범위 사이에 숫자가 1개 있거나 없는 경우가 세번반복 후 . 을 찍고 동일한 패턴을 찾는다.

​

​

2. INSTR(컬럼, '찾을문자', 시작위치, 몇번째) : 시작위치에서부터 지정한 문자를 찾는다

SELECT INSTR('Unbelievable', 'e', 1) FROM DUAL;
> 4

SELECT INSTR('Unbelievable', 'e', 5) FROM DUAL;
> 7

SELECT INSTR('Unbelievable', 'E', 1) FROM DUAL;
> 0

SELECT INSTR('Unbelievable', 'E', 1, 1) FROM DUAL;
> 4

SELECT INSTR('Unbelievable', 'E', 1, 2) FROM DUAL;
> 7

SELECT INSTR('Unbelievable', 'E', 1, 3) FROM DUAL;
> 12

INSTR과 SUBSTR을 혼합하여 사용하면 필요한 문자열 출력이 가능하다. 

SELECT SUBSTR(컬럼1, INSTR(컬럼1, '찾을 문자', 시작위치')) 를 통해 '컬럼1에서 찾을 문자로부터 뒷 내용 모두 출력이 가능하다.

-- SUBSTR('쿼리로 문자열 다루기', 5) 
SELECT SUBSTR('쿼리로 문자열 다루기' , INSTR('쿼리로 문자열 다루기', '문',1) ) FROM DUAL
> 문자열 다루기

 

 

 

3. CONCAT('컬럼1','컬럼2') : 문자열 연결

SELECT CONCAT('Unbelievable','Shot') FROM DUAL;
> UnbelievableShot

--아래와 같이도 가능
SELECT 'Unbelievable'||' '||'Shot' FROM DUAL;
> Unbelievable Shot

 

4. INITCAP, UPPER, LOWER : 대소문자 변환

--구분자를 기준으로 대문자 변환을 한다
SELECT INITCAP('ase zed d') FROM DUAL;
> Ase Zed D

SELECT UPPER('ase zed d') FROM DUAL;
> ASE ZED D

SELECT LOWER('ASE ZED D') FROM DUAL;
> ase zed d

 

 

5. LPAD , RPAD 문자열채우기

SELECT LPAD('Shot', 10) FROM DUAL;
> 'Shot '

SELECT LPAD('Shot', 10, '!') FROM DUAL;
> '!!!!!!Shot'

SELECT RPAD('Shot', 10, '?') FROM DUAL;
> 'Shot??????'

--한글의 경우는 2BYTE 차지하여 10->5문자로 인식 '진짜'=4BYTE, '?'*6
SELECT RPAD('진짜', 10, '?') FROM DUAL;
> '진짜??????'

 

 

6. LTRIM, RTRIM : 좌/우측에서부터 문자열 제거

SELECT LTRIM('1234567') FROM DUAL;
> '1234567'

SELECT LTRIM('1234567', ' ') FROM DUAL;
> '1234567'

SELECT LTRIM('1234567', '12') FROM DUAL;
> '34567'
--12로 시작하기 때문에TRIM 안됨
SELECT LTRIM('1234567', '34') FROM DUAL;
> '1234567'

 

7. REPLACE('문자열', 'Search Char', 'Replace Char') : 문자열 대체

--결과 동일
SELECT REPLACE('Unbelievable', 'Un', '') FROM DUAL;
SELECT REPLACE('Unbelievable', 'Un') FROM DUAL;
SELECT REPLACE('Unbelievable', 'Un', NULL) FROM DUAL;
> believable

 

​

8. LOB데이터 다루기 : 4000바이트 이상의 컬럼인 경우 CLOB 또는 BLOB 타입으로 저장할 수 있는데 SELECT에서 그대로 출력을 할수가 없다. 단순 SELECT를 통해서 확인을 하고 싶은경우 DBMS_LOB.SUBSTR(컬럼, 문자열길이, 시작위치) 로 가져올 수 있다.

LOB데이터 관련해서는 차후 다른 포스팅으로 다뤄볼 예정이다.