1. 숫자 관련 함수들
- ROUND: 반올림
- CEIL: 올림
- FLOOR: 내림
SELECT
ROUND(0.5),
CEIL(0.4),
FLOOR(0.6);
![](https://velog.velcdn.com/images/jeong_yooony/post/f08ea918-64e8-4435-b61e-aa810b622225/image.png)
SELECT
Price,
ROUND(price),
CEIL(price),
FLOOR(price)
FROM Products;
![](https://velog.velcdn.com/images/jeong_yooony/post/4f280474-da04-481b-bae5-8228a305fe4e/image.png)
SELECT ABS(1), ABS(-1), ABS(3 - 10);
![](https://velog.velcdn.com/images/jeong_yooony/post/5cc169d4-3333-4a0d-b863-f8a823394a09/image.png)
SELECT * FROM OrderDetails
WHERE ABS(Quantity - 10) < 5;
![](https://velog.velcdn.com/images/jeong_yooony/post/fa65d170-8294-4845-96dd-14631dbc4b60/image.png)
- GREATEST: (괄호 안에서) 가장 큰 값
- LEAST: (괄호 안에서) 가장 작은 값
SELECT
GREATEST(1, 2, 3),
LEAST(1, 2, 3, 4, 5);
![](https://velog.velcdn.com/images/jeong_yooony/post/754b9418-f1f5-4707-989d-293a7c1f289a/image.png)
SELECT
OrderDetailID, ProductID, Quantity,
GREATEST(OrderDetailID, ProductID, Quantity),
LEAST(OrderDetailID, ProductID, Quantity)
FROM OrderDetails;
![](https://velog.velcdn.com/images/jeong_yooony/post/da5688a7-a1bc-4632-969a-6f50ebf05065/image.png)
💡 그룹 함수 - 조건에 따라 집계된 값을 가져옵니다.
- MAX: 가장 큰 값
- MIN:가장 작은 값
- COUNT: 갯수 (NULL값 제외)
- SUM: 총합
- AVG: 평균 값
SELECT
MAX(Quantity),
MIN(Quantity),
COUNT(Quantity),
SUM(Quantity),
AVG(Quantity)
FROM OrderDetails
WHERE OrderDetailID BETWEEN 20 AND 30;
![](https://velog.velcdn.com/images/jeong_yooony/post/9aad1863-915e-4bd8-9682-f28222dd06a7/image.png)
- POW(A, B), POWER(A, B): A를 B만큼 제곱
- SQRT: 제곱근
SELECT
POW(2, 3),
POWER(5, 2),
SQRT(16);
![](https://velog.velcdn.com/images/jeong_yooony/post/cfb5f41e-f332-410a-a957-2d67aed2237e/image.png)
SELECT Price, POW(Price, 1/2)
FROM Products
WHERE SQRT(Price) < 4;
![](https://velog.velcdn.com/images/jeong_yooony/post/ff99f41f-4c97-43b4-8d86-a34f1a3a95df/image.png)
- TRUNCATE(N, n): N을 소숫점 n자리까지 선택
SELECT
TRUNCATE(1234.5678, 1),
TRUNCATE(1234.5678, 2),
TRUNCATE(1234.5678, 3),
TRUNCATE(1234.5678, -1),
TRUNCATE(1234.5678, -2),
TRUNCATE(1234.5678, -3);
![](https://velog.velcdn.com/images/jeong_yooony/post/c7e1fdf3-25b5-48b4-afa4-14532b31f38f/image.png)
SELECT Price FROM Products
WHERE TRUNCATE(Price, 0) = 12;
![](https://velog.velcdn.com/images/jeong_yooony/post/221a15b7-dc32-4d8d-b630-097f7a1a1e97/image.png)
🐬 더 많은 숫자 함수 보러가기 ▶️
2. 문자열 관련 함수들
- UCASE, UPPER: 모두 대문자로
- LCASE, LOWER: 모두 소문자로
SELECT
UPPER('abcDEF'),
LOWER('abcDEF');
![](https://velog.velcdn.com/images/jeong_yooony/post/2ffa34fd-45e2-4e89-8675-430dc5874afc/image.png)
SELECT
UCASE(CustomerName),
LCASE(ContactName)
FROM Customers;
![](https://velog.velcdn.com/images/jeong_yooony/post/94bf5e44-a110-4c90-954e-65b154ef9b12/image.png)
- CONCAT(...): 괄호 안의 내용 이어붙임
- CONCAT_WS(S, ...): 괄호 안의 내용 S로 이어붙임
SELECT CONCAT('HELLO', ' ', 'THIS IS ', 2021)
![](https://velog.velcdn.com/images/jeong_yooony/post/921ab7e2-9f1e-43c7-96fc-e7835a6e6b89/image.png)
SELECT CONCAT_WS('-', 2021, 8, 15, 'AM')
![](https://velog.velcdn.com/images/jeong_yooony/post/65ab215f-3507-4908-97ba-566c695ff12e/image.png)
SELECT CONCAT('O-ID: ', OrderID) FROM Orders;
![](https://velog.velcdn.com/images/jeong_yooony/post/cc1dd4d3-3133-4648-b18b-195d170df0e6/image.png)
SELECT
CONCAT_WS(' ', FirstName, LastName) AS FullName
FROM Employees;
![](https://velog.velcdn.com/images/jeong_yooony/post/44ebcd37-35e8-411c-a8e4-64704774a7f5/image.png)
- SUBSTR, SUBSTRING: 주어진 값에 따라 문자열 자름
- LEFT: 왼쪽부터 N글자
- RIGHT: 오른쪽부터 N글자
SELECT
SUBSTR('ABCDEFG', 3),
SUBSTR('ABCDEFG', 3, 2),
SUBSTR('ABCDEFG', -4),
SUBSTR('ABCDEFG', -4, 2);
![](https://velog.velcdn.com/images/jeong_yooony/post/5f6c3873-350b-4462-b78b-9e3b732e4e15/image.png)
SELECT
LEFT('ABCDEFG', 3),
RIGHT('ABCDEFG', 3);
![](https://velog.velcdn.com/images/jeong_yooony/post/9522fea5-a28d-4e09-a296-4b8f610ff769/image.png)
SELECT
OrderDate,
LEFT(OrderDate, 4) AS Year,
SUBSTR(OrderDate, 6, 2) AS Month,
RIGHT(OrderDate, 2) AS Day
FROM Orders;
![](https://velog.velcdn.com/images/jeong_yooony/post/37fabf5f-5faf-4c65-904c-d9223ea0e89a/image.png)
- LENGTH: 문자열의 바이트 길이
- CHAR_LENGTH, CHARACTER_LEGNTH: 문자열의 문자 길이
SELECT
LENGTH('ABCDE'),
CHAR_LENGTH('ABCDE'),
CHARACTER_LENGTH('ABCDE');
![](https://velog.velcdn.com/images/jeong_yooony/post/bc4d1cd6-6c2d-4799-b220-5b8bb525029d/image.png)
-- w3wchool 사이트에서는 한글이 제대로 동작하지 않습니다.
SELECT
LENGTH('안녕하세요'), -- 15
CHAR_LENGTH('안녕하세요'), -- 5
CHARACTER_LENGTH('안녕하세요'); -- 5
![](https://velog.velcdn.com/images/jeong_yooony/post/e52c5c10-ea38-40a7-a6f8-3c4c553a230b/image.png)
- TRIM: 양쪽 공백 제거
- LTRIM: 왼쪽 공백 제거
- RTRIM: 오른쪽 공백 제거
SELECT
CONCAT('|', ' HELLO ', '|'),
CONCAT('|', LTRIM(' HELLO '), '|'),
CONCAT('|', RTRIM(' HELLO '), '|'),
CONCAT('|', TRIM(' HELLO '), '|');
![](https://velog.velcdn.com/images/jeong_yooony/post/d2f617e0-157f-478d-b577-254b7c80cf68/image.png)
SELECT * FROM Categories
WHERE CategoryName = ' Beverages '
![](https://velog.velcdn.com/images/jeong_yooony/post/58ae5164-89cb-4b33-bf04-5a02384b22b2/image.png)
SELECT * FROM Categories
WHERE CategoryName = TRIM(' Beverages ')
![](https://velog.velcdn.com/images/jeong_yooony/post/e72e017a-812e-4468-8e9b-237053d1a293/image.png)
- LPAD(S, N, P): S가 N글자가 될 때까지 P를 이어붙임
- RPAD(S, N, P): S가 N글자가 될 때까지 P를 이어붙임
SELECT
LPAD('ABC', 5, '-'),
RPAD('ABC', 5, '-');
![](https://velog.velcdn.com/images/jeong_yooony/post/b75f9b5b-e1a0-4cc9-b774-85c7b3e55920/image.png)
SELECT
LPAD(SupplierID, 5, 0),
RPAD(Price, 6, 0)
FROM Products;
![](https://velog.velcdn.com/images/jeong_yooony/post/589f7d2c-56a4-4eac-a2ca-36fd156b78af/image.png)
- REPLACE(S, A, B): S중 A를 B로 변경
SELECT
REPLACE('맥도날드에서 맥도날드 햄버거를 먹었다.', '맥도날드', '버거킹');
![](https://velog.velcdn.com/images/jeong_yooony/post/5e89a61a-fc83-42e7-ba6d-e911bf1fe942/image.png)
SELECT
REPLACE(Description, ', ', ' and ')
FROM Categories;
![](https://velog.velcdn.com/images/jeong_yooony/post/dec3c687-5f53-431b-96fa-83e63728b038/image.png)
- INSTR(S, s): S중 s의 첫 위치 반환, 없을 시 0
SELECT
INSTR('ABCDE', 'ABC'),
INSTR('ABCDE', 'BCDE'),
INSTR('ABCDE', 'C'),
INSTR('ABCDE', 'DE'),
INSTR('ABCDE', 'F');
![](https://velog.velcdn.com/images/jeong_yooony/post/344b58f6-a448-4e6b-bcde-f5ca51043bf5/image.png)
SELECT * FROM Customers
WHERE INSTR(CustomerName, ' ') BETWEEN 1 AND 6;
-- < 6으로 하면?
![](https://velog.velcdn.com/images/jeong_yooony/post/8d7f3948-a7a1-47b8-ae04-763592d47b05/image.png)
- CAST(A AS T): A를 T 자료형으로 변환
- CONVERT(A, T): A를 T 자료형으로 변환
SELECT
'01' = '1',
CAST('01' AS DECIMAL) = CAST('1' AS DECIMAL);
![](https://velog.velcdn.com/images/jeong_yooony/post/4f06c968-56f7-40da-8dbd-94b61909db5c/image.png)
SELECT
'01' = '1',
CONVERT('01', DECIMAL) = CONVERT('1', DECIMAL);
![](https://velog.velcdn.com/images/jeong_yooony/post/b2573843-d96c-4348-bea8-9f64a2b6adb7/image.png)
🐬 더 많은 문자열 함수 보러가기 ▶️