MySQL 내장함수 정리

주형(Jureamer)·2023년 2월 27일

문자열 함수

CONCAT(), CONCAT_WS() : 두 개 이상의 문자열을 결합합니다.

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

# CONCAT_WS는 특정 구분자를 이용해 문자를 결합할 수 있음
SELECT CONCAT_WS('/', '2022', '10','22'); -- '2022/10/22'

SUBSTRING() : 문자열의 일부분을 추출합니다.

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

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

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

LENGTH() : 문자열의 길이를 반환합니다.

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

LOWER(), UPPDER() : 문자열을 모두 소문자, 대문자로 변환합니다.

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

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

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

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"

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-02-27'); -- 2023

# 날짜에 대한 "월"을 반환
SELECT MONTH('2023-02-27'); -- 2

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

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

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

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

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

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

TIME_TO_SEC(time) : 시간을 초 단위로 반환니다.

SELECT TIME_TO_SEC('12:00:00'); -- 43200

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

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

MySQL DATE_FORMAT 옵션값 참고

숫자 관련 함수

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

SELECT SUM(sales) FROM orders; -> 10000

AVG(column) : 숫자 열의 평균을 계산합니다.

SELECT AVG(sales) FROM orders; -> 500

MAX(column) : 숫자 열의 최댓값을 찾습니다.

SELECT MAX(sales) FROM orders; -> 1000

MIN(column) : 숫자 열의 최솟값을 찾습니다.

SELECT MIN(sales) FROM orders; -> 100

COUNT(column) : 열의 레코드 수를 반환합니다.

SELECT COUNT(*) FROM orders; -> 10

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

TRUNCATE(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, n): 숫자를 n 제곱한 값을 반환합니다.

SELECT POW(5, 3); -- 125

RAND() : 0 ~ 1 사이의 랜덤값을 반환합니다.

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

조건함수

IF(expr, true_value, false_value) : 조건에 따라 다른 값을 반환합니다.

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

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

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

CASE expression WHEN value1 THEN result1 WHEN value2 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:]+$)