1. 대소문자 변환 함수
UPPER 함수
- 기능: 모든 문자를 대문자로 변환합니다.
- 사용법: UPPER(문자열)
- 예시: UPPER('Hello World') → 'HELLO WORLD'
LOWER 함수
- 기능: 모든 문자를 소문자로 변환합니다.
- 사용법: LOWER(문자열)
- 예시: LOWER('Hello World') → 'hello world'
INITCAP 함수
- 기능: 각 단어의 첫 글자만 대문자로, 나머지는 소문자로 변환합니다.
- 사용법: INITCAP(문자열)
- 예시: INITCAP('hello world') → 'Hello World'
2. 문자열 조작 함수
CONCAT 함수
- 기능: 두 문자열을 연결합니다.
- 사용법: CONCAT(문자열1, 문자열2)
- 예시: CONCAT('Hello', 'World') → 'HelloWorld'
- 참고: || 연산자로도 동일한 기능을 수행할 수 있습니다. 예: 'Hello' || 'World'
SUBSTR 함수
- 기능: 문자열의 일부를 추출합니다.
- 사용법: SUBSTR(문자열, 시작위치, [길이])
- 예시:
- SUBSTR('HelloWorld', 1, 5) → 'Hello'
- SUBSTR('HelloWorld', 6) → 'World'
- 참고: 오라클에서 문자열의 인덱스는 1부터 시작합니다.
LENGTH 함수
- 기능: 문자열의 길이를 반환합니다.
- 사용법: LENGTH(문자열)
- 예시: LENGTH('Hello World') → 11
INSTR 함수
- 기능: 문자열 내에서 특정 문자나 문자열의 위치를 찾습니다.
- 사용법: INSTR(대상문자열, 찾을문자열, [시작위치], [발생횟수])
- 예시:
- INSTR('HelloWorld', 'o') → 5 (첫 번째 'o'의 위치)
- INSTR('HelloWorld', 'o', 6) → 7 (6번째 위치부터 검색했을 때 'o'의 위치)
LPAD와 RPAD 함수
- 기능: 문자열의 왼쪽(LPAD) 또는 오른쪽(RPAD)에 지정된 문자를 채워 전체 길이를 맞춥니다.
- 사용법:
- LPAD(문자열, 전체길이, [채울문자])
- RPAD(문자열, 전체길이, [채울문자])
- 예시:
- LPAD('Hello', 10, '*') → '*****Hello'
- RPAD('Hello', 10, '*') → 'Hello*****'
LTRIM과 RTRIM 함수
- 기능: 문자열의 왼쪽(LTRIM) 또는 오른쪽(RTRIM)에서 지정된 문자를 제거합니다.
- 사용법:
- LTRIM(문자열, [제거할문자집합])
- RTRIM(문자열, [제거할문자집합])
- 예시:
- LTRIM(' Hello') → 'Hello' (기본값으로 공백 제거)
- LTRIM('xxxHello', 'x') → 'Hello'
TRIM 함수
- 기능: 문자열의 양쪽(또는 왼쪽/오른쪽)에서 지정된 문자를 제거합니다.
- 사용법: TRIM([LEADING|TRAILING|BOTH] [제거할문자] FROM 문자열)
- 예시:
- TRIM(' Hello ') → 'Hello'
- TRIM(LEADING 'x' FROM 'xxxHello') → 'Hello'
3. 문자열 대체 함수
REPLACE 함수
- 기능: 문자열 내의 특정 문자나 문자열을 다른 문자나 문자열로 대체합니다.
- 사용법: REPLACE(문자열, 찾을문자열, [대체할문자열])
- 예시:
- REPLACE('Hello World', 'World', 'Oracle') → 'Hello Oracle'
- REPLACE('Hello World', 'World') → 'Hello ' (대체할 문자열을 지정하지 않으면 제거됨)
TRANSLATE 함수
- 기능: 문자열에서 지정된 문자들을 다른 문자들로 일대일 대응하여 변환합니다.
- 사용법: TRANSLATE(문자열, 찾을문자집합, 대체할문자집합)
- 예시: TRANSLATE('Hello', 'elo', '123') → 'H1223' (e→1, l→2, o→3으로 변환)
4. 특수 문자 함수
ASCII 함수
- 기능: 문자의 ASCII 코드 값을 반환합니다.
- 사용법: ASCII(문자)
- 예시: ASCII('A') → 65
CHR 함수
- 기능: ASCII 코드 값에 해당하는 문자를 반환합니다.
- 사용법: CHR(숫자)
- 예시: CHR(65) → 'A'
5. 문자열 형식 함수
TO_CHAR 함수 (문자열 변환)
- 기능: 숫자나 날짜를 지정된 형식의 문자열로 변환합니다.
- 사용법: TO_CHAR(값, [형식], [nls_parameters])
- 예시:
- TO_CHAR(1234.56, '9,999.99') → '1,234.56'
- TO_CHAR(SYSDATE, 'YYYY-MM-DD') → '2025-03-29'
6. 실제 활용 예시
이름 형식 변경하기
-- 'JOHN DOE'를 'John Doe'로 변환
SELECT INITCAP('JOHN DOE') FROM DUAL;
이메일에서 도메인 추출하기
-- 'user@example.com'에서 도메인 부분만 추출
SELECT SUBSTR('user@example.com', INSTR('user@example.com', '@') + 1) FROM DUAL;
전화번호 형식 통일하기
-- '010-1234-5678'을 '01012345678'로 변환
SELECT REPLACE('010-1234-5678', '-', '') FROM DUAL;
주민등록번호 마스킹하기
-- '123456-1234567'을 '123456-*******'로 변환
SELECT RPAD(SUBSTR('123456-1234567', 1, 7), 14, '*') FROM DUAL;
7. 문자 함수 사용 시 주의사항
- 대소문자 구분: 오라클은 기본적으로 문자열 비교 시 대소문자를 구분합니다. 대소문자를 구분하지 않고 비교하려면 UPPER나 LOWER 함수를 사용해야 합니다.
- NULL 처리: 문자 함수에 NULL을 입력하면 대부분 NULL을 반환합니다. NVL 또는 COALESCE 함수를 함께 사용하여 NULL을 처리할 수 있습니다.
- 문자셋과 언어 설정: 다국어 환경에서는 NLS(National Language Support) 설정에 따라 결과가 달라질 수 있습니다.
- 성능 고려: 대용량 데이터에서 문자 함수를 사용할 때는 성능에 영향을 줄 수 있으므로 인덱스 사용 여부를 고려해야 합니다.
8. 문자 함수 조합 사용 예시
이메일 유효성 검사
-- 이메일에 '@'와 '.'이 포함되어 있는지 확인
SELECT
email,
CASE
WHEN INSTR(email, '@') > 0 AND INSTR(email, '.', INSTR(email, '@')) > 0
THEN 'Valid'
ELSE 'Invalid'
END AS validity
FROM users;
이름 이니셜 만들기
-- 'John Smith'에서 'J.S.' 형식의 이니셜 만들기
SELECT
name,
UPPER(SUBSTR(name, 1, 1) || '.' ||
SUBSTR(name, INSTR(name, ' ') + 1, 1) || '.') AS initials
FROM employees;