: 입력 값을 기준으로 단일 값을 반환하는 함수
Function | Description |
---|---|
UCASE | 영문을 대문자로 변환 |
LCASE | 영문을 소문자로 변환 |
MID | 문자열 부분을 반환 |
LENGTH | 문자열 길이를 반환 |
ROUND | 지정 자리에서 숫자를 반올림 (0: 소수점 첫째 자리) |
NOW | 현재 날짜 및 시간 반환 |
FORMAT | 숫자를 천단위 ,가 있는 형식으로 반환 |
SELECT UCASE(string);
: 영문을 대문자로 변환
mysql> select UCASE('This Is ucase Test.');
+------------------------------+
| UCASE('This Is ucase Test.') |
+------------------------------+
| THIS IS UCASE TEST. |
+------------------------------+
1 row in set (0.04 sec)
mysql> select ucase(menu) from sandwich where price > 15;
+----------------------------------+
| ucase(menu) |
+----------------------------------+
| LOBSTER ROLL |
| GRILLED LAUGHING BIRD SHRIMP AND |
| SHAVED PRIME RIB |
+----------------------------------+
3 rows in set (0.04 sec)
SELECT LCASE(string);
: 영문을 소문자로 변환
mysql> select lcase('This Is LCASE Test.');
+------------------------------+
| lcase('This Is LCASE Test.') |
+------------------------------+
| this is lcase test. |
+------------------------------+
mysql> select lcase(menu) from sandwich where price < 5;
+--------------+
| lcase(menu) |
+--------------+
| meatball sub |
+--------------+
SELECT MID(string, start_position, length);
:문자열 부분 반환
string :원본 문자열
start : 문자열 반환시작 위치 (첫글자 1, 마지막 -1)
length : 반환할 문자열 길이
mysql> select MID('This is mid test',6,5);
+-----------------------------+
| MID('This is mid test',6,5) |
+-----------------------------+
| is mi |
+-----------------------------+
1 row in set (0.05 sec)
mysql> select mid('This is mid test', -8, 3);
+-------------------------------+
| mid('This is mid test', -8,3) |
+-------------------------------+
| mid |
+-------------------------------+
1 row in set (0.04 sec)
mysql> select MID(cafe, 6, 4) from sandwich where rand = 11;
+-----------------+
| MID(cafe, 6, 4) |
+-----------------+
| Cafe |
+-----------------+
1 row in set (0.04 sec)
SELECT LENGTH(string);
: 문자열 길이 반환
mysql> select length('This is len test');
+----------------------------+
| length('This is len test') |
+----------------------------+
| 16 |
+----------------------------+
1 row in set (0.04 sec)
mysql> select length(address), address from sandwich where rand <= 3;
+-----------------+---------------------+
| length(address) | address |
+-----------------+---------------------+
| 19 | 2109 W. Chicago Ave |
| 18 | 800 W. Randolph St |
| 16 | 445 N. Clark St |
+-----------------+---------------------+
3 rows in set (0.04 sec)
: 지정한 자리에서 반올림
SELECT ROUND(number, decimals_place);
mysql> select round(315.625);
+----------------+
| round(315.625) |
+----------------+
| 316 |
+----------------+
1 row in set (0.04 sec)
# 동일 방식 (첫번째 소수점 : 0)
mysql> select round(315.625, 0);
+-------------------+
| round(315.625, 0) |
+-------------------+
| 316 |
+-------------------+
1 row in set (0.04 sec)
mysql> select round(315.625, 2);
+-------------------+
| round(315.625, 2) |
+-------------------+
| 315.63 |
+-------------------+
1 row in set (0.04 sec)
mysql> select round(315.625, -1);
+--------------------+
| round(315.625, -1) |
+--------------------+
| 320 |
+--------------------+
1 row in set (0.04 sec)
mysql> select round(315.625, -2);
+--------------------+
| round(315.625, -2) |
+--------------------+
| 300 |
+--------------------+
1 row in set (0.04 sec)
: 현재 및 시간을 반환
SELECT NOW();
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2022-11-11 07:56:01 |
+---------------------+
1 row in set (0.04 sec)
: 숫자를 천단위 콤마가 있는 형식으로 반환 --> round와 비슷
SELECT FORMAT(number, decimal_place);
mysql> select format(12345.6789, 0);
+-----------------------+
| format(12345.6789, 0) |
+-----------------------+
| 12,346 |
+-----------------------+
1 row in set (0.04 sec)
mysql> select format (12345.6789, 2);
+------------------------+
| format (12345.6789, 2) |
+------------------------+
| 12,345.68 |
+------------------------+
1 row in set (0.04 sec)