← SQL roadmap

Topic 34 of 52 · Intermediate

Functions Reference: String & Numeric

Every common MySQL string and math function, what it does and an example.

Concept

A function takes input values and returns one value per row. String functions clean and reshape text; numeric functions round and calculate.

Functions can be used in SELECT, WHERE, ORDER BY, GROUP BY and UPDATE. Wrapping an indexed column in a function inside WHERE usually stops the index being used.

Positions in MySQL strings start at 1, not 0.

Cheat sheet

FunctionWhat it doesExample → result
CONCAT(a, b, ...)Joins strings (NULL if any arg is NULL)CONCAT('Ab','cd') → 'Abcd'
CONCAT_WS(sep, a, b)Joins with a separator, skips NULLsCONCAT_WS('-','a','b') → 'a-b'
LENGTH(s)Length in bytesLENGTH('abc') → 3
CHAR_LENGTH(s)Length in charactersCHAR_LENGTH('héllo') → 5
UPPER(s) / LOWER(s)Change caseUPPER('sql') → 'SQL'
TRIM(s) / LTRIM / RTRIMRemove spaces (or chars)TRIM(' hi ') → 'hi'
SUBSTRING(s, pos, len)Part of a stringSUBSTRING('Hyderabad',1,3) → 'Hyd'
LEFT(s, n) / RIGHT(s, n)First / last n charactersRIGHT('2026-10',2) → '10'
SUBSTRING_INDEX(s, d, n)Text before the n-th delimiterSUBSTRING_INDEX('a@b.com','@',1) → 'a'
INSTR(s, sub) / LOCATE(sub, s)Position of a substring (0 if absent)INSTR('mysql','sql') → 3
REPLACE(s, from, to)Replace all occurrencesREPLACE('a-b-c','-','') → 'abc'
REVERSE(s)Reverse a stringREVERSE('abc') → 'cba'
LPAD(s, n, p) / RPADPad to length nLPAD('7',3,'0') → '007'
REPEAT(s, n)Repeat a stringREPEAT('ab',2) → 'abab'
FORMAT(x, d)Number with commas and d decimalsFORMAT(1234567.8,1) → '1,234,567.8'
REGEXP_LIKE / REGEXP_REPLACERegex test / replace (MySQL 8)REGEXP_REPLACE('a1b2','[0-9]','') → 'ab'
ROUND(x, d)Round to d decimalsROUND(3.456,2) → 3.46
TRUNCATE(x, d)Cut decimals without roundingTRUNCATE(3.456,1) → 3.4
CEIL(x) / FLOOR(x)Round up / down to integerCEIL(4.1) → 5
ABS(x)Absolute valueABS(-5) → 5
MOD(a, b) / a % bRemainderMOD(10,3) → 1
POWER(x, y) / SQRT(x)Power / square rootPOWER(2,10) → 1024
RAND()Random number 0–1ORDER BY RAND() LIMIT 1
SIGN(x)-1, 0 or 1SIGN(-8) → -1
GREATEST / LEASTLargest / smallest of a listGREATEST(3,9,4) → 9
CAST(x AS type)Change data type (standard SQL)CAST('42' AS UNSIGNED) → 42
CONVERT(x, type) / CONVERT(s USING cs)Change type or character setCONVERT('3.7', DECIMAL(4,1)) → 3.7
COALESCE(a, b, ...)First non-NULL valueCOALESCE(NULL, 0) → 0
CRC32(s)32-bit checksum of a stringCRC32('MySQL') → 3259397556
REGEXP_SUBSTR(s, pattern)Text matching a regex (MySQL 8)REGEXP_SUBSTR('order#4521','[0-9]+') → '4521'
REGEXP_INSTR(s, pattern)Position of a regex matchREGEXP_INSTR('ab12','[0-9]') → 3

Code (SQL · MySQL 8)

SELECT
  CONCAT(UPPER(LEFT(name, 1)), LOWER(SUBSTRING(name, 2))) AS proper_name,
  SUBSTRING_INDEX(email, '@', -1)                      AS email_domain,
  LPAD(id, 6, '0')                                      AS customer_code,
  REPLACE(phone, ' ', '')                               AS clean_phone
FROM customers;

SELECT order_id,
  ROUND(amount * 1.18, 2) AS with_gst,
  CEIL(amount / 500)      AS slabs,
  MOD(order_id, 2) = 0    AS is_even_id
FROM orders;

Remember

  • String positions start at 1.
  • CONCAT returns NULL if any input is NULL — use CONCAT_WS or COALESCE.
  • ROUND rounds, TRUNCATE just cuts.

Interview questions

How do you extract the domain from an email?

SUBSTRING_INDEX(email, '@', -1) returns everything after the last '@'.

LENGTH vs CHAR_LENGTH?

LENGTH counts bytes; CHAR_LENGTH counts characters. They differ for multi-byte (UTF-8) text.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “Functions Reference: String & Numeric” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.