Managing & Using MySQL, 2nd Edition

Functions

Functions in SQL are similar to functions in other programming languages such as C and Perl. The function takes zero or more arguments and returns some value. For example, the function SQRT(16) returns 4. Within a MySQL SELECT statement, functions may be used in one of two ways:

As a value to be retrieved

This form involves a function in the place of a column in the list of columns to be retrieved. The return value of the function, evaluated for each selected row, is part of the returned result set as if it were a column in the database.^[7]

For example:

SELECT name, FROM_UnixTIME(date) 
FROM events
WHERE time > 90534323

This query selects the name of each event and the date of the event formatted in human-readable form for all events more recent than the given time. FROM_UnixTIME( ) transforms a standard Unix time value into a human-readable form. ^[8]

# The LENGTH(  ) function returns the character length of
# a given string.
SELECT title, text, LENGTH(text)
FROM papers
WHERE author = 'Stacie Sheldon'

This query selects the title of a paper, the full text of the paper, and the length of the text in bytes for all of the papers authored by Stacie Sheldon. The LENGTH( ) function returns the character length of a given string.

As part of a WHERE clause

This form involves a function used in place of a constant when evaluating a WHERE clause. The value of the function is used for comparison for each row of the table. For example:

SELECT name FROM entries WHERE id = ROUND( ...