Contact Us

MySQL Scalar Functions

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

 

 

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…