MySQL Functions

Notes

• Function availability can depend on MySQL version.
• Examples are minimal on purpose—meant for fast recall.

String functions

• CONCAT(a,b,...) – join strings
• CONCAT_WS(sep,a,b,...) – join with separator
• LENGTH(str) – bytes length
• CHAR_LENGTH(str) – character length
• LOWER(str), UPPER(str)
• TRIM(str), LTRIM(str), RTRIM(str)
• SUBSTRING(str,pos,len) (aka SUBSTR)
• REPLACE(str,from,to)
• LOCATE(substr,str) – position (0 if not found)

SELECT CONCAT_WS(' ', first_name, last_name) AS full_name
FROM users;

Numeric functions

• ABS(n), CEIL(n), FLOOR(n), ROUND(n,dec)
• RAND() – random float [0,1)
• MOD(a,b) – remainder

SELECT ROUND(12.3456, 2) AS n; -- 12.35

Date and time

• NOW(), CURRENT_TIMESTAMP
• CURDATE(), CURTIME()
• DATE(dt), TIME(dt)
• DATE_ADD(dt, INTERVAL n unit)
• DATE_SUB(dt, INTERVAL n unit)
• TIMESTAMPDIFF(unit, a, b)
• DATE_FORMAT(dt, fmt)

SELECT DATE_ADD(NOW(), INTERVAL 7 DAY) AS next_week;

Aggregation

• COUNT(*), SUM(x), AVG(x), MIN(x), MAX(x)
• GROUP_CONCAT(x) – concatenate values per group

SELECT status, COUNT(*) AS n
FROM orders
GROUP BY status;

NULL handling

• IFNULL(x, alt) – if x is NULL, return alt
• COALESCE(a,b,...) – first non-NULL
• NULLIF(a,b) – NULL if a=b else a

SELECT COALESCE(nickname, first_name) AS display_name
FROM users;

Control flow

• IF(cond, a, b)
• CASE WHEN ... THEN ... ELSE ... END

SELECT id,
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    ELSE 'C'
  END AS grade
FROM exams;

JSON (when using JSON columns)

• JSON_EXTRACT(doc, path) (aka ->)
• JSON_UNQUOTE(JSON_EXTRACT(...)) (aka ->>)
• JSON_SET(doc, path, val)
• JSON_ARRAY(), JSON_OBJECT()

SELECT JSON_UNQUOTE(JSON_EXTRACT(profile, '$.city')) AS city
FROM users;

Security and hashing

• SHA2(str, 256) – hashing
• MD5(str) – legacy hashing (avoid for passwords)

Quick reminders

• LENGTH vs CHAR_LENGTH: bytes vs characters.
• NOW() returns date+time; CURDATE() date only.
• GROUP_CONCAT has a max length setting (group_concat_max_len).