MySQL 내장함수 정리

문자열 함수

CONCAT, CONCAT_WS : 두 개 이상의 문자열을 결합

SELECT CONCAT('Hello', ' ', 'World');	-- 'Hello World'

# CONCAT_WS는 특정 구분자를 이용해 문자를 결합할 수 있다.
SELECT CONCAT_WS('/', '2023', '12', '26');	-- '2023/12/26'

 

SUBSTR : 문자열의 일부분을 추출

SELECT SUBSTR('Hello World', 7);	-- 'World'

# SUBSTR과 SUBSTRING 둘 다 사용 가능하다.
# 3번 째 인자로 숫자를 기입하면 해당 숫자 길이만큼 문자열을 자른다.
SELECT SUBSTR('Hello World', 7, 3);	-- 'Wor'

 

SUBSTRING_INDEX : 찾는 문자열이 n회 등장하면 그 이후 문자열을 버린다. n이 마이너스면 오른쪽에서 시작

SELECT SUBSTRING_INDEX('test.account.gmail.com', '.', 2);	-- 'test.account'
SELECT SUBSTRING_INDEX('test.account.gmail.com', '.', -2);	-- 'gmail.com'

 

LENGTH : 문자열의 길이를 반환

SELECT LENGTH('Hello World');	-- 11

 

LOWER, UPPER : 문자열을 모두 소문자, 대문자로 변환

SELECT LOWER('Hello World');	-- 'hello world'
SELECT UPPER('Hello World');	-- 'HELLO WORLD'

 

TRIM([LEADING, TRAILING, BOTH][rem_str] FROM str) : 문자열의 앞/뒤/양쪽에서 지정된 문자열 제거

SELECT TRIM("     abc     "); -- 'abc'
# SELECT TRIM(BOTH FROM "     abc     ");도 같은 결과이다.
# 제거할 문자열을 지정하지 않으면 공백이 없어진다.

SELECT TRIM(LEADING "a" FROM "aaaabcbbbaaa"); -- 'bcbbbaaa'
SELECT TRIM(TRAILING "a" FROM "aaaabcbbbaaa"); -- 'aaaabcbb'
SELECT TRIM(BOTH "a" FROM "aaaabcbbbaaa"); -- 'bcbbb'

 

REPLACE(str, old_str, new_str) : 문자열에서 old_str을 new_str로 대체

SELECT REPLACE("It's Banana", "Banana", "Apple"); -- It's Apple

 

INSTR(str, substr) : 문지열에서 substr이 처음 나타나는 위치를 반환

SELECT INSTR('foobarbar', 'bar'); -- 4

# SQL에서 시작점은 1이기 때문에 0이면 존재하지않는다는 의미
SELECT INSTR('xbar', 'foobar'); -- 0

 

LPAD, RPAD(str, len, pad_str) : 문자열을 왼쪽, 오른쪽으로 패딩

SELECT LPAD("aaa", 5, "0"); -- 00aaa
SELECT RPAD("aaa", 5, "0"); -- aa000

 

LEFT, RIGHT(str, len) : 문자열에서 왼쪽 또는 오른쪽에서 길이만큼 추출

SELECT LEFT("abcde", 3); -- "abc"
SELECT RIGHT("abcde", 3); -- "cde"

 

MID(str, pos, len) : 문자열에서 특정 시작점에서의 길이만큼을 추출

SELECT MID("abcdefghi", 5, 2); -- "ef"
# SUBSTR, SUBSTRING과 같음

 

BIN, OCT, HEX : 각각 2진수, 8진수, 16진수 값을 반환

SELECT BIN(31); -- 11111
SELECT OCT(31); -- 37
SELECT HEX(31); -- 1F

 

REVERSE(str) : 주어진 문자열을 거꾸로 반환

SELECT REVERSE("123456789"); -- "987654321"

 

SPACE(len) : 길이만큼의 공백을 반환

SELECT CONCAT("Hello", SPACE(5), "World"); -- "Hello     World"

 

REPEAT(str, len) : 문자열을 주어진 횟수만큼 반복

SELECT REPEAT("abc", 3); -- "abcabcabc"

 

LOCATE(substr, str, [pos]) : 첫 번째로 발견한 문자열의 위치를 반환 (POSITION과 동일, INSTR와는 파라미터 순서만 다름)

SELECT LOCATE("abc", "abcdefabc"); -- 1
SELECT POISITION("abc" IN "abcdefabc"); -- 1
SELECT INSTR("abcdefabc", "abc");

# 시작지점을 정하는 것은 Locate에서만 가능합니다.
SELECT LOCATE("abc", "abcdefabc", 3); -- 7

 

FORMAT(x, d) : 세 자리 수 마다 콤마를 넣고 소수점 이하로 주어진 길이만큼 표시

SELECT FORMAT(12332.1,4); -- '12,332.1000'
SELECT FORMAT(12332.2,0); -- '12,332'

날짜 및 시간 관련 함수

CURDATE, CURTIME, NOW, SYSDATE : 현재 날짜 또는 시간을 반환

# 현재 날짜를 '년-월-일'로 반환
SELECT CURDATE(); -- '2023-02-27'

# 현재 시간을 HH:MM:SS로 반환
SELECT CURTIME(); -- '04:45:10'

# 현재 날짜 및 시간을 반환
SELECT NOW(); -- '2023-02-27 04:45:00'
SELECT SYSDATE(); -- '2023-02-27 04:45:00'

 

YEAR, MONTH, DAYOFMONTH, HOUR, MINUTE, SECOND : 특정 날짜나 시간 단위를 반환

# 날짜에 대한 "연도"를 반환
SELECT YEAR('2023-12-26'); -- 2023

# 날짜에 대한 "월"을 반환
SELECT MONTH('2023-12-26'); -- 12

# 날짜에 대한 "일"을 반환
SELECT DAYOFMONTH('2023-12-26'); -- 26

# 시간을 반환
SELECT HOUR('2023-12-26 15:32:00'); -- 15

# 분을 반환
SELECT MINUTE('2023-12-26 15:32:00'); -- 32

# 초를 반환
SELECT SECOND('2023-12-26 15:32:00'); -- 0

 

ADDDATE(date, diff), SUBDATE(date, diff) : 날짜를 기준으로 차이를 더하거나 뺍니다.

SELECT ADDDATE('2022-12-31', INTERVAL 15 DAY); -- '2022-01-15'
SELECT ADDDATE('2022-12-31', INTERVAL 1 MONTH); -- '2022-01-31'

SELECT SUBDATE('2022-12-31', INTERVAL 15 DAY); -- '2022-12-16'
SELECT SUBDATE('2022-12-31', INTERVAL 1 MONTH); -- '2022-11-30'

 

DATEDIFF(date1, date2), TIMEDIFF(time1, time2) : 날짜 차이 혹은 시간 차이 반환

SELECT DATEDIFF('2022-12-31', '2022-10-29'); -- 63
SELECT TIMEDIFF('14:50:00', '08:40:50'); -- 06:09:10

 

DATEOFWEEK(day) : 해당 날짜의 요일을 숫자로 반환(일:1, 월:2..)

SELECT DAYOFWEEK(NOW()); -- 2

 

MONTHNAME(month) : 해당 월의 영어 이름을 반환

SELECT MONTHNAME(NOW()); -- February

 

DAYOFYEAR(day) : 1월 1일 기준으로 몇 일이 지났는 지를 반환

SELECT DAYOFYEAR(NOW()); -- 58

 

LASTDAY(date) : 주어진 월의 마지막 날을 반환

SELECT LAST_DAY('2012-02-01'); -- '2012-02-29

 

DATE_FORMAT(date, condition) : 조건에 맞는 형식으로 변환된 date를 반환

SELECT DATE_FORMAT(now(), '%Y-%M-%D'); -- '2023-February-27th'

지정자	설명
%a	축약된 요일 이름( Sun.. Sat)
%b	월 이름 약어( Jan.. Dec)
%c	월, 숫자( 0.. 12)
%D	날짜에 영어 접미사( 0th, 1st, 2nd, 3rd, …) 를 붙입니다.
%d	날짜, 숫자( 00.. 31)
%e	날짜, 숫자( 0.. 31)
%f	마이크로초( 000000.. 999999)
%H	시간 ( 00.. 23)
%h	시간 ( 01.. 12)
%I	시간 ( 01.. 12)
%i	분, 숫자( 00.. 59)
%j	올해의 일 ( 001.. 366)
%k	시간 ( 0.. 23)
%l	시간 ( 1.. 12)
%M	월 이름( January.. December)
%m	월, 숫자( 00.. 12)
%p	AM또는PM
%r	시간, 12시간( hh:mm:ss다음에 AM또는 PM)
%S	초( 00.. 59)
%s	초( 00.. 59)
%T	시간, 24시간( hh:mm:ss)
%U	주( 00.. 53), 여기서 일요일은 주의 첫 번째 날입니다. WEEK()모드 0
%u	주( 00.. 53), 여기서 월요일은 주의 첫 번째 날입니다. WEEK()모드 1
%V	주( 01.. 53), 여기서 일요일은 주의 첫 번째 날입니다. WEEK()모드 2; 함께 사용 %X
%v	주( 01.. 53), 여기서 월요일은 주의 첫 번째 날입니다. WEEK()모드 3; 함께 사용 %x
%W	요일 이름 ( Sunday.. Saturday)
%w	요일( 0=일요일.. 6=토요일)
%X	일요일이 주의 첫날인 주의 연도, 숫자, 4자리. 함께 사용%V
%x	월요일이 주의 첫 번째 날인 주의 연도, 숫자, 4자리. 함께 사용%v
%Y	연도, 숫자, 4자리
%y	연도, 숫자(2자리)
%%	문자 그대로의 %문자
%x	x, 위에 나열되지 않은 " x" 의 경우

숫자 관련 함수

SUM(column) : 숫자 열의 합계를 계산

SELECT SUM(sales) FROM orders;

 

AVG(column) : 숫자 열의 평균을 계산

SELECT AVG(sales) FROM orders;

 

MAX(column) : 숫자 열의 최댓값을 찾음

SELECT MAX(sales) FROM orders;

 

MIN(column) : 숫자 열의 최솟값을 찾음

SELECT MIN(sales) FROM orders;

 

COUNT(column) : 열의 레코드 수를 반환

SELECT COUNT(*) FROM orders;

 

ABS(number) : 숫자의 절대값을 출력

SELECT ABS(100-150);  -- 50

 

MOD(분자, 분모) : 분자를 분모로 나눈 나머지를 출력(%와 동일)

SELECT MOD(100, 3); -- 1

 

CEILING(number) : 소수점을 올림한 값을 반환

SELECT CEILING(3301.3123); -- 3302

 

FLOOR(number) : 소수점을 내림한 값을 반환

SELECT FLOOR(1523.5779); -- 1523

 

TRUNDATE(number, pos) : 숫자를 소수점 이하 자리수에서 버린 값을 반환

SELECT TRUNCATE(1523.5779, 2); -- 1523.57
SELECT TRUNCATE(1523.5779, -2); -- 1500

 

ROUND(number, pos) : 숫자를 소수점 이하 자리수에서 반올림한 값을 반환

SELECT ROUND(1523.5779, 3); -- 1523.578

 

SQRT(number) : 숫자의 제곱근 값을 반환

SELECT SQRT(100); -- 10

 

POW(number, pos) : 숫자에 pos 제곱한 값을 반환

SELECT POW(5, 3); -- 125

 

RAND : 0 ~ 1사이의 랜덤값을 반환

SELECT FLOOR(RAND() * 100)+ 1; -- 1 ~ 100 사이 랜덤한 값 반환

조건함수

IF(condition, true_value, false_value) : 조건에 따라 다른 값을 반환

SELECT IF(sales > 500, 'High', 'Low') FROM orders; -- 'Low'

 

IFNULL(column, value) : 컬럼이 NULL이라면 대체할 값을 출력

SELECT IFNULL(data, '자료없음') FROM library; -- '자료없음'

 

CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE else_result END : 다중 조건에 따라 다른 값을 반환

SELECT CASE
	WHEN sales > 500 
        THEN 'High' 
        WHEN sales > 100 
        THEN 'Medium' 
        ELSE 'Low' END 
   FROM orders; -- 'Medium'

 

COALESCE(value1, value2, ...) : NULL이 아닌 첫 번째 인수를 반환

SELECT COALESCE(NULL, NULL, 'apple', 'banana'); -- 'apple'

정규식

매칭

• `.` : 문자 하나, ex) `...` - 문자열의 길이가 세글자 이상인 것
• `|` : 또는 (OR) |로 구분된 문자에 해당하는 문자열을 찾음 ex) `a|b`라면 a 또는 b에 해당하는 문자열을 찾음
• `[]` : `[]`안에 나열된 패턴에 해당하는 문자열을 찾음
• `^` : 시작하는 문자열을 찾음
• `$` : 끝나는 문자열을 찾음

수의 제한

• `*` : 0회 이상 나타나는 문자
• `+` : 1회 이상 나타나는 문자
• `{m, n}` : m회 이상 n회 이하 나타나는 문자
• `?` : 0 또는 1회 나타나는 문자

 문자 그룹

• `[A-z]` 또는 `[:alpha:]` 또는 `\a` : 알파벳 대문자 또는 소문자인 문자열을 찾음
• `[0-9]` 또는 `[:digit:]` 또는 `\d` : 숫자인 문자열을 찾음

부정

• `[^문자]` : 괄호 안의 문자를 포함하지 않은 문자열을 찾음

# (1) apple 또는 banana로 시작하는 문자열을 찾고 싶을 때

SELECT *
FROM tb
WHERE data REGEXP ('^apple | ^banana')


# (2) 길이 7글자인 문자열 중 2번째 자리부터 abc를 포함하는 문자열을 찾고 싶을 때

SELECT * 
FROM tb
WHERE data REGEXP ('^.abc...$')

# (3) 텍스트와 숫자가 섞여있는 문자열에서 숫자로만 이루어진 문자열을 찾고 싶을 때

SELECT *
FROM tb
WHERE data REGEXP (^[:digit:]+$)

Leference

• MySQL Functions
• [MYSQL] 📚 내장함수 종류 정리
• 정규표현식 (Regular Expression) 이해하기