함수
DBMS 함수 사용 원칙
DB는 보편적으로 일관성 및 무결성 유지를 위해 단일 DB로 설계하는 경우가 많다. (분산 설계는 특수)
이 때 함수 사용은 집합 처리•필터링•기본 집계(필요 시 윈도우•다차원 집계)와 같은 결정적•인덱스 친화 영역으로 한정하여, 포맷팅•문자열 결합•복잡한 비즈니스 규칙은 애플리케이션 계층으로 위임한다.
| 구분 | 설명 |
|---|---|
| 권장 함수 | 집계(통계) → SUM, AVG, COUNT, MIN, MAX 다차원 집계 → GROUP BY ROLLUP, GROUPING SETS, 윈도우/랭킹 → ROW_NUMBER, RANK, DENSE_RANK, LAG/LEAD NULL 처리 → COALESCE, NULLIF 기간 버킷팅/추출 → DATE_TRUNC, EXTRACT 문자열 집계/결합 → STRING_AGG, CONCAT_WS 단순 매핑 → CASE 최댓값/최솟값 선택 → GREATEST, LEAST JSON 존재성/키 조회 → @>, ?, `? |
| 지양 패턴 | 표현/포맷팅 → TO_CHAR, FORMAT, 화면용 CONCAT 인덱스 무력화 필터 → WHERE LOWER(col)=..., SUBSTRING(col,...) 비결정/시간의존 필터 → random(), now()을 조건/조인키에 사용 고비용 정규식/대량 치환 → REGEXP_REPLACE, REGEXP_MATCHES 남용 컬럼 산술 변환 비교 → col + interval '9h' < now(), col + 1 = 3 JSON 깊은 경로 직접 필터 → GIN 없이->> 조건 남용 |
문자 처리 함수
| 함수 | 설명 | 결과값 |
|---|---|---|
ASCII('A') | 첫 문자에 대한 ASCII 코드 반환 | 65 |
CHR(65) | ASCII 코드에 해당하는 문자 반환 | 'A' |
CHAR_LENGTH('가나다') | 문자 수 반환 | 3 |
OCTET_LENGTH('가') | 바이트 길이 반환 (UTF-8 기준 한글 3바이트) | 3 |
CONCAT('A','B') | 문자열 연결 | 'AB' |
CONCAT_WS('-', 'A','B') | 구분자 포함 연결 | 'A-B' |
FORMAT('Hello, %s %s', 'A','B') | 서식 문자열로 포맷팅 | 'Hello, A B' |
TO_CHAR(12345.678, 'FM999G999D00') | 숫자/날짜 서식화 | '12,345.68' |
LEFT('abcdef', 3) | 왼쪽에서 n글자 추출 | 'abc' |
RIGHT('abcdef', 2) | 오른쪽에서 n글자 추출 | 'ef' |
SUBSTRING('abcdef' FROM 2 FOR 3) | 부분 문자열 추출 | 'bcd' |
POSITION('cd' IN 'abcde') | 부분 문자열 위치 (1부터 시작) | 3 |
STRPOS('abcde','cd') | 부분 문자열 위치 (POSITION과 동일 기능) | 3 |
OVERLAY('abcde' PLACING 'X' FROM 3 FOR 1) | 지정 구간을 다른 문자열로 덮어쓰기 (=삽입/치환) | 'abXde' |
REPLACE('abab','a','x') | 모든 매칭 문자열 치환 | 'xbxb' |
TRANSLATE('2019-01-01','-','/') | 문자 단위 치환 | '2019/01/01' |
LPAD('1', 3, '0') | 왼쪽 패딩 | '001' |
RPAD('hi', 5, '!') | 오른쪽 패딩 | 'hi!!!' |
TRIM(' hi ') | 양쪽 공백 제거 | 'hi' |
LTRIM('---hi','-') | 왼쪽 지정 문자 제거 | 'hi' |
RTRIM('hi!!!','!') | 오른쪽 지정 문자 제거 | 'hi' |
LOWER('XYZ') | 소문자 변환 | 'xyz' |
UPPER('xyz') | 대문자 변환 | 'XYZ' |
INITCAP('hello world') | 단어별 첫 글자 대문자화 | 'Hello World' |
SPLIT_PART('a,b,c', ',', 2) | 구분자로 분리 후 n번째 요소 반환 | 'b' |
STRING_AGG(col, ', ') | 여러 행 값을 구분자로 연결 (집계) | 예: 'A, B, C' |
REGEXP_REPLACE('abc123','[0-9]','','g') | 정규식 치환 | 'abc' |
REGEXP_MATCHES('a1b2','[0-9]+') | 정규식 매칭 (집합 반환) | {'1'}, {'2'} |
REPEAT('ab', 3) | 문자열 반복 | 'ababab' |
OCTET_LENGTH
주어진 컬럼 값/문자열의 길이(BYTE) 반환
| 작성법 | 리턴 값 타입 |
|---|---|
| OCTET_LENGTH(CHAR | STRING) | INTEGER |
- CHAR | STRING : 문자 타입 컬럼 또는 문자열
SELECT emp_name, OCTET_LENGTH(emp_name), email, OCTET_LENGTH(email)
FROM employee;CHAR_LENGTH
주어진 컬럼 값/문자열의 길이(문자 개수) 반환
| 작성법 | 리턴 값 타입 |
|---|---|
| CHAR_LENGTH (CHAR | STRING) | INTEGER |
- CHAR | STRING : 문자 타입 컬럼 또는 문자열
SELECT emp_name, CHAR_LENGTH(emp_name),
email, CHAR_LENGTH(email) FROM employee;POSITION
지정한 위치부터 지정한 숫자 번째로 나타나는 문자의 시작 위치 반환
| 작성법 | 리턴 값 타입 |
|---|---|
| POSITION(str, string, [start]) | NUMBER |
- str : 찾으려는 문자열
- string : 문자 타입 컬럽 또는 문자열
- start : 찾을 위치 시작 값(기본 값 1)
-- EMaIL 컬럼의 문자열 중 '@'의 위치를 구하시오
SELECT email, POSITION('@' IN email) FROM employee;LPAD / RPAD
주어진 컬럼, 문자열에 임의의 문자열을 왼쪽 / 오른쪽에 덧붙여 길이 N의 문자열 반환
| 작성법 | 리턴 값 타입 |
|---|---|
| LPAD(STRING, N, [STR]) / RPAD(STRING, N, [STR]) | CHARACTER |
- STRING : 문자 타입 컬럼 또는 문자열
- N : 반환할 문자(열)의 길이(바이트), 원래 STRING의 길이보다 작다면 N만큼 잘라서 표시
- STR : 덧붙이려는 문자(열), 생략 시 공백 문자
SELECT LPAD(email, 20, '#')
FROM EMPLOYEE;
SELECT RPAD(email, 20, '#')
FROM EMPLOYEE;LTRIM / RTRIM
주어진 컬럼, 문자열의 왼쪽/오른쪽에서 공백을 제거한 나머지 반환
| 작성법 | 리턴 값 타입 |
|---|---|
| LTRIM(STRING / RTRIM(STRING) | CHARACTER |
SELECT emp_name, LTRIM(phone, RTRIM(email) FROM employee;
SELECT LTRIM(' 공백 '), RTRIM(' 공백 '); -- 테이블 이름이 없어도 쿼리 완성REPLACE
주어진 컬럼, 문자열에서 특정 문자열을 찾아 새로운 문자열로 교체
| 작성법 | 리턴 값 타입 |
|---|---|
| REPLACE(string, substring, new_string) | CHARACTER |
- string : 문자 타입 컬럼 또는 문자열
- substring : 찾아서 바꿀 문자열 (old 문자)
- new_string : 새롭게 대체될 문자열 (new 문자)
SELECT emp_name, REPLACE(phone, '010', ''),
REPLACE(EMAIL, '@codeit.com', '@codeit.net') FROM employee;SUBSTR
컬럼이나 문자열에서 지정한 위치부터 지정한 개수의 문자열을 잘라내어 반환
| 작성법 | 리턴 값 타입 |
|---|---|
| SUBSTR(STRING, POSITION, [LENGTH]) | CHARACTER |
- STRING : 문자 타입 컬럼 또는 문자열
- POSITION : 문자열을 잘라낼 위치로 양수면 시작 방향에서 지정한 수 만큼, 음수면 끝 방향에서 지정한 수 만큼의 위치를 의미
- LENGTH : 반환할 문자 개수(생략 시 문자열의 끝까지 의미, 음수면 NULL 리턴)
SELECT SUBSTR('SHOWMETHEMONEY', 5, 2); -- ME
SELECT SUBSTR('SHOWMETHEMONEY', 7); -- THEMONEY
SELECT SUBSTR('SHOWMETHEMONEY', 1, 6); -- SHOWME
SELECT SUBSTR('SHOWMETHEMONEY', -8, 3); -- THE
SELECT SUBSTR('SHOWMETHEMONEY', -10, 2); -- ME LOWER / UPPER
컬럼의 문자 혹은 문자열을 소문자/대문자/첫 글자만 대문자로 변환하여 반환
| 작성법 | 리턴 값 타입 |
|---|---|
| LOWER(STRING) / UPPER(STRING) | CHARACTER |
- STRING : 문자 타입 컬럼 또는 문자열
SELECT LOWER('Welcome To My World'); -- welcome to my world
SELECT UPPER('Welcome To My World'); -- WELCOME TO MY WORLDCONCAT
컬럼의 문자 혹은 문자열을 두 개 전달 받아 하나로 합친 후 반환
| 작성법 | 리턴 값 타입 |
|---|---|
| CONCAT(STRING, STRING ….) | CHARACTER |
- STRING : 문자 타입 컬럼 또는 문자열
SELECT CONCAT('가나다라', 'ABCD'); -- 가나다라ABCD
SELECT '가나다라' || 'ABCD'; -- 가나다라ABCD숫자 처리 함수
| Function | 설명 |
|---|---|
| ABS | 숫자의 절대값을 반환합니다. |
| AVG | 표현식의 평균값을 반환합니다. |
| CEIL | 숫자보다 크거나 같은 가장 작은 정수 값을 반환합니다. |
| CEILING | 숫자보다 크거나 같은 가장 작은 정수 값을 반환합니다. |
| COUNT | 선택 쿼리에서 반환된 레코드 수를 반환합니다. |
| DIV | 정수 나눗셈에 사용 |
| FLOOR | 숫자에 대해 <=인 가장 큰 정수 값을 반환합니다. |
| GREATEST | 인수 목록의 가장 큰 값을 반환합니다. |
| LEAST | 인수 목록의 가장 작은 값을 반환합니다. |
| MAX | 값 집합에서 최대값을 반환합니다. |
| MIN | 값 집합에서 최소값을 반환합니다. |
| MOD | 숫자를 다른 숫자로 나눈 나머지를 반환합니다. |
| RANDOM | 난수를 반환합니다. |
| ROUND | 지정된 소수 자릿수로 숫자를 반올림합니다. |
| SIGN | 숫자의 부호를 반환합니다. |
| SUM | 값 집합의 합계를 계산합니다. |
| TRUNC | 지정된 소수점 이하 자릿수로 숫자를 자릅니다 |
ABS
Backend engineer