📌 내장함수 (Built-In Function)
- MySQL 내부에 만들어둔 함수
- 문자열 함수: 형변환, 부분문자열, 문자개수/바이트수, 비교, 치환, 결측치, ...
- 날짜 함수: 현재 날짜와 시간, 표준 날짜와 시간, 두 날짜의 길이(시간, 분, ...), 연도, 월, 일, 시, 분, 초 정보
- 수학 함수: 반올림, 올림, 내림, 절대값, 사인, 코사인, ...
- 집계 함수: 합계, 평균, 표준편차, 최대, 최소, ...
- 분석 함수: LAG, LEAD(앞뒤 데이터 조회), 누적분포, 상대순위 계산, 첫줄/마지막행
📌 문자열 함수
1️⃣ 문자열 연결 (CONCAT, CONCAT_WS)
- 여러 문자열 하나로 결합
CONCAT(문자열1, 문자열2, ...): 문자열들을 하나로 결합 (공백X)CONCAT_WS(구분자, 문자열1, 문자열2, ...): 구분자를 사이에 두고 결합
SELECT concat('I', 'Love', 'MySQL') AS col1,
concat('I', ' Love', ' MySQL') AS col2,
concat_ws(' ', 'I', 'Love', 'MySQL') AS col3,
concat_ws(',', 'I', 'Love', 'MySQL') AS col4;concat_ws는 python 에서 print 사용시separator를 지정하는 것과 유사하다.
- 출력
SELECT concat_ws(': ', clientName, clientPhone) AS '고객정보'
FROM bookClient;- 출력
2️⃣ 형변환 (CAST, CONVERT)
- 데이터 형식을 명시적으로 변환
CAST(컬럼 AS 데이터유형): 표준 SQL 방식의 형변환CONVERT(컬럼, 데이터유형): MySQL의 확장방식의 형변환 + 문자셋 변환CAST/CONVERT에 사용가능한 데이터 유형- BINARY, CHAR, DATE, DATETIME, TIME, DECIMAL, JSON, NCHAR, SIGNED, UNSIGNED
- 문자열을 숫자 형태로 자동 변환해주는 기능
SIGNED는 부호가 있는 정수 타입으로, 양수와 음수 모두 허용된다.UNSIGNED는 부호가 없는 정수 타입으로, 0이상의 양수만 허용된다.
SELECT CAST('2' AS SIGNED),
CAST('-5' AS SIGNED),
CAST('-5' AS UNSIGNED); - 실제로
UNSIGNED는 음수 입력시 오류가 발생하여 결과가overflow된 정수가 출력되었다.
- 출력
쿼리1
DECIMAL(5, 2)는 전체 자릿수는 5이며 소수점 둘째 자리까지 표시한다. 따라서 정수 부분은 최대 3자리이고, -999.99 에서 999.99 까지 표시할 수 있다.
SELECT cast('123' AS SIGNED) AS col1,
cast('3.14' AS DECIMAL(5, 2)) as col2,
cast(100 AS CHAR) AS col3,
cast('2025-02-04' AS DATE) AS col4,
cast(3.99 AS UNSIGNED) AS col5;쿼리2
SELECT CONVERT('123', SIGNED) AS col1,
CONVERT(100, CHAR) AS col2,
CONVERT('안녕하세요' USING utf8mb4) AS col3,
CONVERT('hello' USING latin1) AS col4,
CONVERT(now(), SIGNED) AS col5;- 출력
| 쿼리1 | 쿼리2 |
|---|---|
 |  |
형변환 적용 사례
1) 문자열데이터를 숫자 변환 후 집계 함수 적용
SELECT sum(cast(price AS DECIMAL(10, 2))) FROM order;2) 날짜문자열을 기준으로 정렬
SELECT * FROM logs WHERE cast(log_date AS DATE) DESC;3) 수치 연산 보장
SELECT '5' / 2,
5 / 2,
cast(5 AS DECIMAL) / 24) 문자 인코딩 대응
SELECT CONVERT(col_name USING utf8mb4) FROM table_name;3️⃣ 결측치 처리
NULL: 값이 존재하지 않음(UNKNOWN)- 결측치 처리 목적
NULL을 다른 값으로 치환- 조건으로
NULL판별 NULL로 인해 계산이 안되는 것 방지
NULL값을 합할 경우 출력 값은NULL이 된다.- 집계함수 사용시
NULL값은 자동으로 제외하고 계산된다.
SELECT 100 + NULL;
SELECT avg(col); # NULL 자동 제외1) 결측치 판단 (ISNULL)
ISNULL(컬럼)- 다음과 같이
NULL값이 포함된 테이블이 있다.
ISNULL은NULL이면1, 아니면0을 출력한다.
SELECT col1, ISNULL(col2), ISNULL(col3) FROM acorn_null; - 출력
2) 결측치 대체 (IFNULL, COALESCE)
-
COALESCE(표현식1, 표현식2, ...):- 왼쪽부터 검사해서 NULL이 아닌 첫번째 값 반환
- 인자 개수 제한 없음
- 표준 SQL 함수
-
IFNULL(표현식, 대체값)- NULL을 지정한 값으로 단순 대체
- MySQL 전용 함수
NULL이 아닌 값으로 첫번째 만나는 값을 반환, 모두NULL이면 '없음' 반환
SELECT col1, COALESCE(col2, col3, col4, col5, '없음') FROM table_null; - 출력
col2값이NULL이 아니면col2값을 사용하고,NULL이면col5값으로 대체한다.
SELECT col1, IFNULL(col2, col5) FROM table_null;- 출력
3) NULL로 변경 (NULLIF)
NULLIF(표현식1, 표현식2):표현식1 = 표현식2이면NULL반환, 다르면표현식1반환- 결측을 의미하는
0, 빈 문자열'', 기본값-1, 자리채움 목적 값'N/A'- NULL로 통일하기 위해
- 다음과 같은 테이블이 있다.
- 1, 4번째 행은
num1이10이기에NULL값이 되어100 / NULL연산을 진행하여 최종 결과값은NULL이 된다.
SELECT 100 / NULLIF(num1, 10) FROM table_null2;- 출력
4️⃣ 소문자, 대문자 변환 (LOWER, UPPER)
- 앞서 사용했던 이 테이블을 다시 보자.
- 각각
upper()대문자 변환,lower()소문자 변환이 적용된 것을 알 수 있다.
SELECT col1, col4, col5, upper(col5), lower(col4) FROM table_null;- 출력
5️⃣ 공백 제거 (TRIM, LTRIM, RTRIM)
- 양쪽, 왼쪽(앞쪽), 오른쪽(뒤쪽)의 공백 혹은 지정된 문자를 제거한다.
SELECT ' hello! MySQL ' AS col1,
trim(' hello! MySQL ') AS trim,
ltrim(' hello! MySQL ')AS ltrim,
rtrim(' hello! MySQL ')AS rtrim;- 출력
trim과BOTH,LEADING,TRAILING+삭제할 문자열조합을 통해 문자열을 삭제할 수 있다. 아래 예시를 보면#만 삭제되고 공백은 남아있는 것을 확인할 수 있다.
SELECT trim(BOTH '#' FROM '# hello! MySQL #') AS col1, # both: 양쪽
trim(LEADING '#' FROM '# hello! MySQL #') AS col2, # LEADING: 앞쪽
trim(TRAILING '#' FROM '# hello! MySQL #') AS col3; # TRAINLING: 뒤쪽- 출력
6️⃣ 문자열 크기/개수 (LENGTH, CHAR_LENGTH)
LENGTH(): 문자열 바이트 수 반환CHAR_LENGTH(): 문자열 개수 반환- 한글은 3바이트, 영문은 1바이트
SELECT length('hello! MySQL'), length('에스큐엘 짱!'),
char_length('hello! MySQL'), char_length('에스큐엘 짱!'); - 출력
- 공백과 특수문자도 포함하여 계산된다.
7️⃣ 특정 문자까지의 길이 (POSITION)
- 지정한 특정 문자까지의 문자열 길이 반환
- 특정문자가 없는 경우
0반환
SELECT 'hello! MySQL', position('!' IN 'hello! MySQL'),
position('#' IN 'hello! MySQL'),
position('my' IN 'hello! MySQL');- 출력
8️⃣ 지정한 길이의 문자열 반환 (LEFT, RIGHT)
LEFT(문자열, 길이): 문자열 왼쪽부터 지정한 길이 만큼의 문자열 반환RIGHT(문자열, 길이): 문자열 오른쪽부터 지정한 길이 만큼의 문자열 반환
SELECT 'hello! MySQL',
LEFT('hello! MySQL', 6),
RIGHT('hello! MySQL', 3);- 출력
9️⃣ 부분문자열 (SUBSTR, SUBSTRING)
SUBSTR(문자열, 시작위치, 길이)SUBSTRING(문자열, 시작위치, 길이)
SELECT 'abc@email.com',
position('@' IN 'abc@email.com'),
substr('abc@email.com', 1, position('@' IN 'abc@email.com') - 1);- 출력
Ex1) 생년월일에서 연도 분리 출력
USE bookdb;
-- 생년월일에서 연도분리
SELECT clientBirth,
substr(clientBirth, 1, position('-' IN clientBirth) - 1)
FROM bookdb.bookClient;- 출력
Ex2) 이름에서 성, 이름 분리 출력
USE marketdb;
-- 고객성명을 성과 이름으로 분리
SELECT customer_name AS '성명',
substr(customer_name, 1, 1) AS '성',
substr(customer_name, 2, 2) AS '이름'
FROM marketdb.customer;- 출력
Ex3) '김' 씨 성을 가진 이름 조회
-- '김'씨 성을 가진 고객이름 조회
SELECT customer_name
FROM marketdb.customer
WHERE substr(customer_name, 1, 1) = '김';1️⃣0️⃣ 문자열 치환 (REPLACE)
REPLACE(문자열, 특정문자열, 대체문자열)
SELECT customer_name, REPLACE(customer_name, '김', 'Kim')
FROM marketdb.customer
WHERE customer_name LIKE '김%';- 출력
1️⃣1️⃣ 문자열 반복 (REPEAT)
REPEAT(문자열, 반복수)
SELECT 'Ha', REPEAT('Ha', 10);- 출력
1️⃣2️⃣ 공백문자생성 (SPACE)
SELECT customer_name,
concat(substr(customer_name, 1, 1),
space(5),
substr(customer_name, 2, 2))
FROM marketdb.customer;- 출력
1️⃣3️⃣ 문자열 역순으로 출력 (REVERSE)
WITH ip_list(ip)
AS(
SELECT '192.168.0.1'
UNION ALL
SELECT '10.6.100.99'
UNION ALL
SELECT '8.8.8.8'
UNION ALL
SELECT '192.200.211.111'
)
SELECT ip, REVERSE(ip),
substr(ip, 1, char_length(ip) - position('.' IN REVERSE(ip)))
FROM ip_list;- 출력
1️⃣4️⃣ 문자열 비교 (STRCMP)
- 두 문자열이 동일하면
0반환, 다르면-1반환
SELECT strcmp('hello! MySQL', 'hello! MySQL'),
strcmp('hello! MySQL', 'hello# MySQL');- 출력
