MySQL Function

MySQL Functions Result Description
SELECT CONCAT(1,' b', 3); '1 b3' Concatenate Fields
SELECT CONCAT_WS(',','First','Last') 'First,Last' Concatenate Fidelds with a Delimiter
SELECT ELT(3, 'a', 'b', 'c', 'd'); 'c' Select a field based on an index
SELECT FIELD('c', 'a', 'b', 'c', 'd'); 3 Find the index
SELECT COALESCE(NULL, NULL, 'a', 'b'); 'a' Return the first non-Null value
SELECT FORMAT(10000.12345, 2); '10,000.12' Format a value
SELECT FIND_IN_SET('b','a,b,c,d'); 2 Find the index of the first parameter within the second parameter
SELECT CAST(3.2 AS CHAR); '3.2' Change to character type
SELECT CAST('2010-01-11 12:30:00' AS DATETIME);   Cast to datatime
SELECT LPAD('24',4,'00'); '0024' Left Pad
SELECT REPLACE('aaaa', 'a', 'b') 'bbbb' Replace string
SELECT ISNULL(1); 0 Test if it is null
SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL; 1, 1, 0 Comparison
SELECT GREATEST(1, 4, 3); 4 Find the max
SELECT LEAST(1, 4, 3); 1 Find the min

MySQL Control Function

SQL Result Description
SELECT CASE val WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END; 'one' Case statement
SELECT IF(1>2,1,2); 2 If statement

MySQL Time function

SQL Result
SELECT ADDDATE('2009-01-01', INTERVAL 31 DAY); Add 31 days
SELECT ADDTIME('2009-01-01 10:10:10', '1 1:0:0'); Add one day and an hour
SELECT CURDATE(); Current date
SELECT CURTIME(); Current time
SELECT DATE('2009-01-01 01:00:00'); Show the date
SELECT DATEDIFF(d1, d2); Date difference
SELECT '2010-01-01 01:00:00' + INTERVAL 1 day ; Add one day
SELECT DATE_FORMAT('2010-01-01 01:00:00', '%H:%i:%s'); Format a date to string
SELECT CAST('2008-09-17 15:30:00' AS DATETIME); Convert to Datetime
SELECT FROM_UNIXTIME(120000000); Convert Unix time to datetime
SELECT MAKEDATE(2010,31); Create a date
SELECT NOW(); Current time
SELECT STR_TO_DATE('01,1,2010','%d,%m,%Y'); Convert to date
SELECT UNIX_TIMESTAMP(); Current Unix time value
SELECT UNIX_TIMESTAMP('2010-01-01 01:00:00'); Unix time
SELECT DAYNAME(date); Monday, Tuesday ....
SELECT DAYOFYEAR(date); Day from beginning of year