Functions

String Functions

CONCAT - Concatenate strings

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

LENGTH - String length

SELECT LENGTH(column_name) FROM $TABEL_NAME;

UPPER - Convert to uppercase

SELECT UPPER(column_name) FROM $TABEL_NAME;

LOWER - Convert to lowercase

SELECT LOWER(column_name) FROM $TABEL_NAME;

SUBSTRING - Extract substring

SELECT SUBSTRING(column_name, start_position, length) FROM $TABEL_NAME;

LEFT - Extract left characters

SELECT LEFT(column_name, number_of_chars) FROM $TABEL_NAME;

RIGHT - Extract right characters

SELECT RIGHT(column_name, number_of_chars) FROM $TABEL_NAME;

TRIM - Remove leading/trailing spaces

SELECT TRIM(column_name) FROM $TABEL_NAME;

LTRIM - Remove leading spaces

SELECT LTRIM(column_name) FROM $TABEL_NAME;

RTRIM - Remove trailing spaces

SELECT RTRIM(column_name) FROM $TABEL_NAME;

REPLACE - Replace substring

SELECT REPLACE(column_name, 'old_string', 'new_string') FROM $TABEL_NAME;

LOCATE - Find position of substring

SELECT LOCATE('substring', column_name) FROM $TABEL_NAME;

REVERSE - Reverse string

SELECT REVERSE(column_name) FROM $TABEL_NAME;

REPEAT - Repeat string

SELECT REPEAT(column_name, number_of_times) FROM $TABEL_NAME;

LPAD - Left pad string

SELECT LPAD(column_name, total_length, 'pad_string') FROM $TABEL_NAME;

RPAD - Right pad string

SELECT RPAD(column_name, total_length, 'pad_string') FROM $TABEL_NAME;

---

Numeric Functions

ABS - Absolute value

SELECT ABS(column_name) FROM $TABEL_NAME;

ROUND - Round to decimal places

SELECT ROUND(column_name, decimal_places) FROM $TABEL_NAME;

CEIL/CEILING - Round up

SELECT CEIL(column_name) FROM $TABEL_NAME;
SELECT CEILING(column_name) FROM $TABEL_NAME;

FLOOR - Round down

SELECT FLOOR(column_name) FROM $TABEL_NAME;

MOD - Modulo operation

SELECT MOD(column_name, divisor) FROM $TABEL_NAME;

POWER/POW - Power function

SELECT POWER(column_name, exponent) FROM $TABEL_NAME;
SELECT POW(column_name, exponent) FROM $TABEL_NAME;

SQRT - Square root

SELECT SQRT(column_name) FROM $TABEL_NAME;

RAND - Random number

SELECT RAND() FROM $TABEL_NAME;

GREATEST - Maximum value

SELECT GREATEST(column1, column2, column3) FROM $TABEL_NAME;

LEAST - Minimum value

SELECT LEAST(column1, column2, column3) FROM $TABEL_NAME;

TRUNCATE - Truncate decimal

SELECT TRUNCATE(column_name, decimal_places) FROM $TABEL_NAME;

SIGN - Sign of number

SELECT SIGN(column_name) FROM $TABEL_NAME;

---

Date/Time Functions

NOW - Current date and time

SELECT NOW() FROM $TABEL_NAME;

CURDATE - Current date

SELECT CURDATE() FROM $TABEL_NAME;

CURTIME - Current time

SELECT CURTIME() FROM $TABEL_NAME;

DATE - Extract date part

SELECT DATE(column_name) FROM $TABEL_NAME;

TIME - Extract time part

SELECT TIME(column_name) FROM $TABEL_NAME;

YEAR - Extract year

SELECT YEAR(column_name) FROM $TABEL_NAME;

MONTH - Extract month

SELECT MONTH(column_name) FROM $TABEL_NAME;

DAY - Extract day

SELECT DAY(column_name) FROM $TABEL_NAME;

HOUR - Extract hour

SELECT HOUR(column_name) FROM $TABEL_NAME;

MINUTE - Extract minute

SELECT MINUTE(column_name) FROM $TABEL_NAME;

SECOND - Extract second

SELECT SECOND(column_name) FROM $TABEL_NAME;

DAYNAME - Day name

SELECT DAYNAME(column_name) FROM $TABEL_NAME;

MONTHNAME - Month name

SELECT MONTHNAME(column_name) FROM $TABEL_NAME;

DAYOFWEEK - Day of week (1-7)

SELECT DAYOFWEEK(column_name) FROM $TABEL_NAME;

DAYOFYEAR - Day of year

SELECT DAYOFYEAR(column_name) FROM $TABEL_NAME;

WEEK - Week number

SELECT WEEK(column_name) FROM $TABEL_NAME;

DATEDIFF - Date difference

SELECT DATEDIFF(date1, date2) FROM $TABEL_NAME;

TIMEDIFF - Time difference

SELECT TIMEDIFF(time1, time2) FROM $TABEL_NAME;

DATE_ADD - Add time interval

SELECT DATE_ADD(column_name, INTERVAL 1 DAY) FROM $TABEL_NAME;
SELECT DATE_ADD(column_name, INTERVAL 1 MONTH) FROM $TABEL_NAME;
SELECT DATE_ADD(column_name, INTERVAL 1 YEAR) FROM $TABEL_NAME;

DATE_SUB - Subtract time interval

SELECT DATE_SUB(column_name, INTERVAL 1 DAY) FROM $TABEL_NAME;
SELECT DATE_SUB(column_name, INTERVAL 1 MONTH) FROM $TABEL_NAME;
SELECT DATE_SUB(column_name, INTERVAL 1 YEAR) FROM $TABEL_NAME;

DATE_FORMAT - Format date

SELECT DATE_FORMAT(column_name, '%Y-%m-%d') FROM $TABEL_NAME;
SELECT DATE_FORMAT(column_name, '%d/%m/%Y') FROM $TABEL_NAME;

STR_TO_DATE - Convert string to date

SELECT STR_TO_DATE('2023-12-25', '%Y-%m-%d') FROM $TABEL_NAME;

UNIX_TIMESTAMP - Convert to timestamp

SELECT UNIX_TIMESTAMP(column_name) FROM $TABEL_NAME;

FROM_UNIXTIME - Convert from timestamp

SELECT FROM_UNIXTIME(column_name) FROM $TABEL_NAME;

---

Conditional Functions

IF - Simple conditional

SELECT IF(condition, true_value, false_value) FROM $TABEL_NAME;

CASE - Complex conditional

SELECT CASE 
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE default_result
END FROM $TABEL_NAME;

IFNULL - Handle NULL values

SELECT IFNULL(column_name, 'default_value') FROM $TABEL_NAME;

NULLIF - Return NULL if equal

SELECT NULLIF(column1, column2) FROM $TABEL_NAME;

COALESCE - Return first non-NULL value

SELECT COALESCE(column1, column2, column3, 'default') FROM $TABEL_NAME;

ISNULL - Check if NULL

SELECT ISNULL(column_name) FROM $TABEL_NAME;

---

JSON Functions (MySQL 5.7+)

JSON_EXTRACT - Extract JSON value

SELECT JSON_EXTRACT(json_column, '$.key') FROM $TABEL_NAME;
SELECT json_column->'$.key' FROM $TABEL_NAME;

JSON_UNQUOTE - Remove quotes from JSON value

SELECT JSON_UNQUOTE(JSON_EXTRACT(json_column, '$.key')) FROM $TABEL_NAME;
SELECT json_column->>'$.key' FROM $TABEL_NAME;

JSON_OBJECT - Create JSON object

SELECT JSON_OBJECT('key1', value1, 'key2', value2) FROM $TABEL_NAME;

JSON_ARRAY - Create JSON array

SELECT JSON_ARRAY(value1, value2, value3) FROM $TABEL_NAME;

JSON_CONTAINS - Check if JSON contains value

SELECT JSON_CONTAINS(json_column, '"value"', '$.key') FROM $TABEL_NAME;

JSON_KEYS - Get JSON keys

SELECT JSON_KEYS(json_column) FROM $TABEL_NAME;

JSON_LENGTH - Get JSON length

SELECT JSON_LENGTH(json_column) FROM $TABEL_NAME;

JSON_TYPE - Get JSON type

SELECT JSON_TYPE(json_column) FROM $TABEL_NAME;

JSON_VALID - Validate JSON

SELECT JSON_VALID(json_column) FROM $TABEL_NAME;

JSON_INSERT - Insert JSON value

SELECT JSON_INSERT(json_column, '$.new_key', 'new_value') FROM $TABEL_NAME;

JSON_SET - Set JSON value

SELECT JSON_SET(json_column, '$.key', 'new_value') FROM $TABEL_NAME;

JSON_REPLACE - Replace JSON value

SELECT JSON_REPLACE(json_column, '$.key', 'new_value') FROM $TABEL_NAME;

JSON_REMOVE - Remove JSON key

SELECT JSON_REMOVE(json_column, '$.key') FROM $TABEL_NAME;

JSON_MERGE - Merge JSON objects

SELECT JSON_MERGE(json_column1, json_column2) FROM $TABEL_NAME;

JSON_SEARCH - Search in JSON

SELECT JSON_SEARCH(json_column, 'one', 'search_value') FROM $TABEL_NAME;

JSON_ARRAYAGG - Aggregate to JSON array

SELECT JSON_ARRAYAGG(column_name) FROM $TABEL_NAME GROUP BY group_column;

JSON_OBJECTAGG - Aggregate to JSON object

SELECT JSON_OBJECTAGG(key_column, value_column) FROM $TABEL_NAME GROUP BY group_column;