CategoryFunctionDescriptionExample
String FunctionsCONCAT(str1, str2, ...)Concatenates two or more strings.SELECT CONCAT('Hello', ' ', 'World');
 LENGTH(str)Returns the length of a string in bytes.SELECT LENGTH('Hello');
 CHAR_LENGTH(str)Returns the length of a string in characters.SELECT CHAR_LENGTH('Hello');
 LOWER(str)Converts the string to lowercase.SELECT LOWER('HELLO');
 UPPER(str)Converts the string to uppercase.SELECT UPPER('hello');
 TRIM([remstr] FROM str)Removes spaces or specified characters from both ends of a string.SELECT TRIM(' Hello ');
 SUBSTRING(str, start, length)Extracts a substring from a string.SELECT SUBSTRING('Hello World', 1, 5);
 REPLACE(str, from_str, to_str)Replaces occurrences of a substring with another substring.SELECT REPLACE('Hello World', 'World', 'MySQL');
 INSTR(str, substr)Returns the position of the first occurrence of a substring.SELECT INSTR('Hello World', 'World');
Numeric FunctionsABS(x)Returns the absolute value of a number.SELECT ABS(-5);
 ROUND(x, d)Rounds a number to d decimal places.SELECT ROUND(5.6789, 2);
 FLOOR(x)Returns the largest integer less than or equal to x.SELECT FLOOR(5.67);
 CEIL(x) or CEILING(x)Returns the smallest integer greater than or equal to x.SELECT CEIL(5.1);
 RAND()Generates a random float value between 0 and 1.SELECT RAND();
 MOD(x, y)Returns the remainder of x divided by y.SELECT MOD(10, 3);
Date and Time FunctionsNOW()Returns the current date and time.SELECT NOW();
 CURDATE()Returns the current date.SELECT CURDATE();
 CURTIME()Returns the current time.SELECT CURTIME();
 DATE_ADD(date, INTERVAL value unit)Adds a time interval to a date.SELECT DATE_ADD('2024-12-24', INTERVAL 5 DAY);
 DATE_SUB(date, INTERVAL value unit)Subtracts a time interval from a date.SELECT DATE_SUB('2024-12-24', INTERVAL 5 DAY);
 DATEDIFF(date1, date2)Returns the number of days between two dates.SELECT DATEDIFF('2024-12-24', '2024-12-20');
 YEAR(date)Extracts the year from a date.SELECT YEAR('2024-12-24');
 MONTH(date)Extracts the month from a date.SELECT MONTH('2024-12-24');
 DAY(date)Extracts the day from a date.SELECT DAY('2024-12-24');
Aggregate FunctionsCOUNT(*)Returns the number of rows in a result set.SELECT COUNT(*) FROM users;
 SUM(expression)Returns the sum of values in a column.SELECT SUM(price) FROM products;
 AVG(expression)Returns the average of values in a column.SELECT AVG(price) FROM products;
 MIN(expression)Returns the minimum value in a column.SELECT MIN(price) FROM products;
 MAX(expression)Returns the maximum value in a column.SELECT MAX(price) FROM products;
Control Flow FunctionsIF(expr1, expr2, expr3)Returns expr2 if expr1 is true, otherwise returns expr3.SELECT IF(5 > 3, 'True', 'False');
 CASE WHENPerforms a conditional case expression.SELECT CASE WHEN age > 18 THEN 'Adult' ELSE 'Minor' END AS status FROM users;
Conversion FunctionsCAST(expr AS type)Converts an expression to a specified data type.SELECT CAST('123' AS SIGNED);
 CONVERT(expr, type)Converts an expression to a specified data type (similar to CAST).SELECT CONVERT('2024-12-24' USING utf8);
 HEX(str)Returns the hexadecimal representation of a string.SELECT HEX('hello');
 UNHEX(str)Converts a hexadecimal string to its original string.SELECT UNHEX('68656C6C6F');
NULL FunctionsIFNULL(expr1, expr2)Returns expr2 if expr1 is NULL, otherwise returns expr1.SELECT IFNULL(NULL, 'default');
 COALESCE(expr1, expr2, ...)Returns the first non-NULL value in the list of expressions.SELECT COALESCE(NULL, NULL, 'Default');