[SQL] 문자함수 - SUBSTR, INSTR, TRIM, REPLACE 등 핵심 정리

함수	설명
INITCAP(문자열)	첫 글자는 대문자, 나머지는 소문자로 변환
LOWER(문자열)	모두 소문자로 변환
UPPER(문자열)	모두 대문자로 변환
LENGTH(문자열)	문자열의 문자 수 반환
LENGTHB(문자열)	문자열의 바이트 수 반환(Oracle 전용)
CONCAT(문자열1, 문자열2)	문자열 2개 연결
SUBSTR(문자열, 시작위치[, 길이])	문자 수 기준으로 부분 문자열 추출
SUBSTRB(문자열, 시작바이트[, 길이])	바이트 수 기준으로 부분 문자열 추출(Oracle 전용)
INSTR(문자열, 찾을문자열[, 시작위치[, 몇번째]])	특정 문자열 위치 반환
INSTRB(문자열, 찾을문자열[, 시작바이트[, 몇번째]])	특정 문자열 위치를 바이트 기준으로 반환(Oracle 전용)
LPAD(문자열, 전체길이[, 채울문자])	왼쪽을 채워 전체 길이 맞춤
RPAD(문자열, 전체길이[, 채울문자])	오른쪽을 채워 전체 길이 맞춤
LTRIM(문자열[, 제거문자])	왼쪽 공백 또는 특정 문자 제거
TRIM([제거문자 FROM] 문자열)	양쪽 공백 또는 특정 문자 제거
REPLACE(문자열, 찾을문자열[, 바꿀문자열])	특정 문자열 치환
TRANSLATE(문자열, 원본문자열, 바꿀문자열)	문자 단위 1:1 치환

1. INITCAT, LOWER, UPPER

• INITCAT (문자열) : 첫 글자는 대문자, 나머지는 소문자로 변환
• LOWER(문자열) : 모두 소문자로 변환
• UPPER(문자열) : 모두 대문자로 변환

INITCAT('hello world') -- Hello world
LOWER('HELLO WORLD') -- hello world
UPPER('hello world') --HELLO WORLD

 

2. LENGTH, LENGTHB

• LENGTH(문자열) : 문자 수
• LENGTHB(문자열) : 바이트 수

LENGTH('한글')-- 2
LENGTHB('한글')-- 6 (UTF-8 기준)

 

3. CONCAT

• CONCAT(문자열1, 문자열2) : 문자열 연결

CONCAT('Hello','World')-- HelloWorld
'Hello'||'World'-- HelloWorld

 

4. SUBSTR, SUBSTRB

• SUBSTR(문자열, 시작위치, 길이) : 문자 기준
• SUBSTRB(문자열, 시작바이트, 길이) : 바이트 기준

SUBSTR('Hello',1,2)-- He
SUBSTR('Hello',2)-- ello

 

5. INSTR, INSTRB

• INSTR(문자열, 찾을문자열) : 위치 반환
• INSTRB(...) : 바이트 기준

INSTR('Hello World','o')-- 5
INSTR('Hello World','o',1,2)-- 8

 

6. LPAD, RPAD

• LPAD(문자열, 전체길이[, 채울문자열]) : 왼쪽을 채워 전체 길이 맞춤
• RPAD(문자열, 전체길이[, 채울문자열]) : 오른쪽을 채워 전체 길이 맞춤

LPAD('123', 5, '0')    -- 00123
RPAD('123', 5, '0')    -- 12300
LPAD('123', 7, 'ab')   -- abab123
RPAD('123', 7, 'ab')   -- 123abab

 

7. TRIM, LTRIM,RTRIM

• TRIM([제거문자 FROM] 문자열) : 양쪽 공백 또는 특정 문자 제거
• LTRIM(문자열[, 제거문자열]) : 왼쪽 공백 또는 특정 문자 제거
• RTRIM(문자열[, 제거문자열]) : 오른쪽 공백 또는 특정 문자 제거

TRIM('   hello   ')-- hello
TRIM('x'FROM'xxxhelloxx')-- hello

LTRIM('   hello')-- hello
LTRIM('abc123','abc')-- 123

RTRIM('hello   ')-- hello
RTRIM('123abc','abc')-- 123

 

8. REPLACE, TRANSLATE

• REPLACE(문자열, 찾을문자열, 바꿀문자열) : 치환
• TRANSLATE(문자열, 원본문자열, 바꿀문자열) : 문자 1:1 치환(바꿀 문자열이 더 짧으면 남는 문자는 삭제됨)

REPLACE('Hello World','World','SQL')-- Hello SQL

TRANSLATE('ABC', 'ABC', '123') -- 123
TRANSLATE('ABCDE', 'ABC', '12') -- 12DE (C는 삭제됨)
TRANSLATE('HELLO', 'EL', '12') -- H121O

구분	REPLACE	TRANSLATE
치환 단위	문자열 전체	문자 1개씩
매핑 방식	통째로 일치해야 치환	각 문자 1:1 대응
대응 없는 문자	그대로	삭제됨
주 사용	단어/구문 치환	문자 마스킹, 암호화