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:

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