Numeric Functions

This YouTube video was created by Steve Griffith.

Numeric Functions are used to alter the returned data of those columns using a number datatype. Numeric Functions, like String Functions, do not alter the actual data that is stored in the database as all functions are executed after the data is retrieved but before it is displayed.

RAND() Function

The RAND() function returns a random decimal number between 0 and 1.

-- Returns a random decimal between 0 and 1 
-- for each row in the movies table
SELECT RAND() AS num
FROM `movies`;

Try it yourself on DB Fiddle

ROUND() Function

The ROUND() function will round a number to the specified number of decimal places. If the number of a decimal place is NOT specified a whole number will be returned.

-- Returns either 0 or 1 for each row in the movies table
SELECT ROUND(RAND()) AS num
FROM `movies`;

-- Returns a random whole number between 0 and 10
-- for each row in the movies table
SELECT ROUND(RAND() * 10) AS num
FROM `movies`;

Try it yourself on DB Fiddle

FLOOR() Function

The FLOOR() function will always round a number down to the previous whole number.

-- Returns a random number from 0 to 1 that is rounded down.
-- This will result in all 0s
SELECT FLOOR(RAND()) AS num
FROM `movies`;

Try it yourself on DB Fiddle

CEIL() Function

The CEIL() function will always round a number up to the next whole number.

-- Returns a random number from 0 to 1 that is rounded up.
-- This will result in all 1s
SELECT CEIL(RAND()) AS num
FROM `movies`;

Try it yourself on DB Fiddle

RADIANS() Function

The RADIANS() function will convert degrees into radians.

-- Returns the number of radians in 90 and 180 degrees
SELECT RADIANS(90) AS ninety, RADIANS(180) as oneeighty
FROM `movies`;

Try it yourself on DB Fiddle

DEGREES() Function

The DEGREES() function will convert radians into degrees.

-- Return the number of degrees in Pi
SELECT DEGREES(3.141592653589793) AS pi
FROM `movies`;

Try it yourself on DB Fiddle

POWER() Function

The POWER() function will return the provided number raised to the specified power.

-- Return the genre_id raised to the power of 2
-- for each movie
SELECT POWER(genre_id, 2)
FROM `movies`;

Try it yourself on DB Fiddle

CONV() Function

The CONV() function will convert numbers between different bases.

-- Covert genre_id from base 10 to binary
SELECT CONV(genre_id, 10, 2) AS bin
FROM `movies`;

Try it yourself on DB Fiddle