eharetea

An overview of some built-in functions in MySQL.

Some important functions in MySQL.

Date Created:Friday December 29th, 2006 03:41 AM
Date Modified:Friday August 01st, 2008 01:02 AM

SELECT CONCAT(first_name, ' ',last_name) FROM users;

+-----------------------------------+
| CONCAT(first_name, ' ',last_name) |
+-----------------------------------+
| Dan Lynch                         |
+-----------------------------------+

# use AS to create aliases

SELECT CONCAT(first_name, ' ',last_name) AS Name FROM users;

+-----------+
| Name      |
+-----------+
| Dan Lynch |
+-----------+



# function in a function:

SELECT LENGTH(CONCAT(first_name, ' ',last_name)) AS Length FROM users;
SELECT UPPER(CONCAT(first_name, ' ',last_name)) AS Name FROM users;



# prints characters 1 through 1 of firstname:

SELECT SUBSTRING(first_name,1,1) AS Name FROM users;



# this sorts the query based on the length of the characters in first_name:

SELECT LENGTH(first_name) AS L, last_name FROM users ORDER BY L DESC LIMIT 1;



# the RAND() function:

SELECT * FROM folders ORDER BY RAND();



# query for a random folder:

SELECT * FROM folders ORDER BY RAND() LIMIT 1;



# FORMAT will add comma's every three digits and a decimal with the number of decimal places specified

SELECT FORMAT(folder_id,2) FROM folders;



# MOD() is modulus:

SELECT MOD(folder_id,10) FROM folders;

# you can also just use the % operator!
SELECT folder_id%10 FROM folders;

other numeric functions: ABS(), CEILING(), FLOOR(), SIGN(), SORT()