This SQL Tutorial provides you a summary of some of the most common MySQL Scalar Functions. For this lesson’s exercises, use this link.
MySQL Scalar Functions allow you to perform different calculations on data values. These functions operate on single rows only and produce one result per row. There are different types of Scalar Functions, this tutorial covers the following:
- String functions – functions that perform operations on character values.
- Numeric functions – functions that perform operations on numeric values.
- Date functions – functions that perform operations on date values.
- Conversion functions – functions that convert column data types.
- NULL-related Functions – functions for handling null values.
Note: this tutorial focuses on MySQL Scalar Functions; for more details about Group Functions, use this link.
MySQL String Functions
|
Syntax |
Description |
Function |
SELECT CONCAT('Hello' , 'World')
-- Result: 'HelloWorld'
|
Returns text strings concatenated |
CONCAT |
SELECT INSTR('hello' , 'e')
-- Result: 2
|
Returns the location of a substring in a string. |
INSTR |
SELECT LENGTH('hello')
-- Result: 5
|
Returns the number of characters of the specified string expression. |
LENGTH |
SELECT RTRIM(' hello ')
-- Result: ' hello'
|
Returns a character string after truncating all trailing blanks. |
RTRIM |
SELECT LTRIM(' hello ')
-- Result: 'hello '
|
Returns a character expression after it removes leading blanks. |
LTRIM |
SELECT REPLACE('hello' , 'e' , '$')
-- Result: 'h$llo'
|
Replaces all occurrences of a specified string value with another string value. |
REPLACE |
SELECT REVERSE('hello')
-- Result: 'olleh'
|
Returns the reverse order of a string value. |
REVERSE |
SELECT SUBSTR('hello' , 2,3)
-- Result: 'ell'
|
Returns part of a text. |
SUBSTR |
SELECT LOWER('HELLO')
-- Result: 'hello'
|
Returns a character expression after converting uppercase character data to lowercase. |
LOWER |
SELECT UPPER('hello')
-- Result: 'HELLO'
|
Returns a character expression with lowercase character data converted to uppercase. |
UPPER |
SELECT SELECT CONCAT('hello', space(3), 'world')
-- Result: 'hello world'
|
Returns a string consisting of N spaces |
SPACE |
MySQL Date Functions
| Syntax |
Description |
Function |
SELECT DATE_ADD('2008-01-02', INTERVAL 1 DAY);
-- Result : 2008-01-03
SELECT DATE_ADD('2008-01-02', INTERVAL 1 WEEK);
-- Result : 2008-01-09
SELECT DATE_ADD('2008-01-02', INTERVAL 1 MONTH);
-- Result : 2008-02-02
|
Returns a specified date with additional time values. |
DATE_ADD |
SELECT DAYOFMONTH('2015-08-30')
-- Result : 30
|
Returns an integer representing the day (day of the month) of the specified date. |
DAYOFMONTH |
SELECT LAST_DAY('2015-08-02')
-- Result : 2015-08-31
|
Returns a date representing the last day of the month for specified date. |
LAST_DAY |
SELECT DATEDIFF('2010-04-01', '2010-03-01')
-- Result : 31
|
Returns the difference between two days, expressed as a value in days. |
DATEDIFF |
SELECT PERIOD_DIFF(201005, 201003) -- Result : 2
|
returns the number of months between two periods. |
PREIOD_DIFF |
SELECT SYSDATE -- Result: (current date)
|
Returns the current database system date. This value is derived from the operating system of the computer on which the instance of MySQL is running. |
SYSDATE() |
MySQL Numeric Functions
|
Syntax |
Description |
Function |
SELECT FLOOR(59.9) -- Result: 59 |
Returns an integer that is less than or equal to the specified numeric expression. |
FLOOR |
SELECT CEIL(59.1) -- Result: 60 |
Returns an integer that is greater than, or equal to, the specified numeric expression. |
CEIL |
SELECT ROUND(59.9) -- Result: 60 SELECT ROUND(59.1) -- Result: 59 |
Returns a numeric value, rounded to the specified length or precision. |
ROUND |
MySQL Conversion Functions
|
Syntax |
Description |
Function |
SELECT DATE_FORMAT(SYSDATE(), '%Y-%m-%d') -- 2015-09-16 |
Converts a a date into a string |
DATE_FORMAT |
SELECT FORMAT(1003423 , 3) -- Result : 1,003,423.000
|
Converts a number into a string |
FORMAT |
SELECT CONVERT('11', UNSIGNED INTEGER)
-- Result 11
|
Used to convert one datatype into another, may be used to convert a string into a number |
CONVERT |
MySQL NULL-Related Functions
|
Syntax |
Description |
Function |
SELECT IFNULL(NULL, 'Hello') -- Hello |
Accepts two arguments and returns the first if its not NULL |
IFNULL |
