Functions¶

Each database engine has a different set of built-in functions that allows us to manipulate data. In this section, we are going to describe some popular functions per data type:

Numeric functions in Mysql¶

ABS¶

Returns the absolute value of a number.

For Instance:

SELECT ABS(-4.5)

Returns:

4.5

CEIL¶

Returns the smallest integer value that is >= to a number For Instance:

SELECT CEIL(4.5)

Returns:

DIV¶

Used for integer division. For Instance:

SELECT FLOOR(7/2)

Returns:

FLOOR¶

Returns the largest integer value that is <= to a number For Instance:

SELECT FLOOR(4.5)

Returns:

GREATEST¶

Returns the greatest value of the list of arguments For Instance:

SELECT GREATEST(4.5, 5.5)

Returns:

5.5

LEAST¶

Returns the smallest value of the list of arguments For Instance:

SELECT LEAST(4.5, 5.5)

Returns:

4.5

String functions in Mysql¶

LENGTH¶

Returns the length of a string (in characters):

SELECT LENGTH(Name)
FROM Person

will return the lengths of the names of persons. For instance, if we had two records with names “Mark Grayson” and “Eve Wilkins”:

12
11

CONCAT¶

Adds two or more srings together:

SELECT CONCAT(Person.Name, Order.OrderId)
FROM Person INNER JOIN ORDER ON Person.PersonId = Order.Person_fk

will return a string concatenating the name of the person and the order ID. For instance:

"Mark Grayson1123"

CONCAT_WS¶

Adds two or more strings together with a separator. If you tried the function above, you would probably get the feeling that the result is not pretty. It is better to concat with a separator:

SELECT CONCAT(' - ', Person.Name, Order.OrderId)
FROM Person INNER JOIN ORDER ON Person.PersonId = Order.Person_fk

This will create a string of the type:

"Mark Grayson - 1123"

LCASE¶

Converts a string to lower-case. For instance:

SELECT LCASE(Name)
FROM Person

with the data above would return:

"mark grayson"
"eve wilkins"

LEFT¶

Extracts a number of characters from a string (starting from left):

SELECT LEFT(Name, 3)
FROM Person

with the data above would return:

"mar"
"eve"

LOCATE¶

Returns the position of the first occurrence of a substring in a string. For instance, say we would like to know which is the position of the first space:

SELECT LOCATE(" ", Name)
FROM Person

5
4

LPAD¶

Left-pads a string with another string, to a certain length:

For instance:

SELECT LPAD("EDEM", 20, "#");

Returns:

"################EDEM"

POSITION¶

Returns the position of the first occurrence of a substring in a string

SELECT POSITION("M" IN "EDEM");

Returns:

REPLACE¶

Replaces all occurrences of a substring within a string, with a new substring

SELECT REPLACE("EDEM", "E", "A");

Returns:

"ADAM"

REVERSE¶

Reverses a string and returns the result:

SELECT REVERSE("EDEM");

Returns:

"MEDE"

RIGHT¶

Extracts a number of characters from a string (starting from right).

SELECT RIGHT("MySQL is Awesome", 3);

Returns:

"ome"

RPAD¶

Right-pads a string with another string, to a certain length. For instance:

SELECT RPAD("EDEM", 10, "#");

Returns:

"EDEM######"

RTRIM¶

Removes trailing spaces from a string:

SELECT RTRIM("EDEM     ");

Returns:

“EDEM”

STRCMP¶

Compares two strings:

SELECT STRCMP("EDEM", "EDEM");

Returns: 0

SUBSTR¶

Extracts a substring from a string (starting at any position):

SELECT SUBSTR("EDEM", 1, 2);

Returns:

"ED"

TRIM¶

Removes leading and trailing spaces from a string:

SELECT TRIM("  EDEM    ");

Returns:

"EDEM"

UCASE¶

Converts a string to upper-case:

SELECT UCASE("edem", 1, 2);

Returns:

"EDEM"

Date type functions¶

ADDTIME¶

Adds a time interval to a time/datetime and then returns the time/datetime

DATEDIFF¶

Returns the number of days between two date values

DATE_ADD¶

Adds a time/date interval to a date and then returns the date

DATE_FORMAT¶

Formats a date

DATE_SUB¶

Subtracts a time/date interval from a date and then returns the date

DAY¶

Returns the day of the month for a given date

DAYNAME¶

Returns the weekday name for a given date

HOUR¶

Returns the hour part for a given date

LOCALTIME¶

Returns the current date and time

NOW¶

Returns the current date and time

TIME_FORMAT Formats a time by a specified format¶

TIMEDIFF¶

Returns the difference between two time/datetime expressions

TIMESTAMP¶

Returns a datetime value based on a date or datetime value

WEEKOFYEAR¶

Returns the week number for a given date

YEARWEEK¶

Returns the year and week number for a given date

Conditional statement functions¶

This section describes another set of functions allow us to build programming logic into our statements.

IF¶

The IF function returns one value if the condition is met, otherwise another value:

# IF(condition, value_if_true, value_if_false)
SELECT IF(500<1000, "True", "False");

IFNULL¶

The IFNULL function returns the value of a field or expression if it is not null, otherwise it returns a default value provided as parameter:

# IFNULL(value, value_if_null)
SELECT IFNULL(Order.Person_fk, "N/A");

CASE¶

Case statements allow us to build more sophisticated conditional statements.

For instance, imagine we want to add a new column “Category” to our query, labeling orders into three categories “Large”, “Medium”, and “Small”, depending on the quantity of the order:

SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN "Large"

    WHEN Quantity = 30 THEN "Medium"
    ELSE "small"
END AS Category
FROM OrderDetails;