String & Numeric Functions Interview Questions & Answers

SQL offers both a standard operator and functions for string concatenation. The || operator is ANSI standard; CONCAT() is supported everywhere and handles NULLs differently.

-- ANSI standard: || operator (Postgres, SQL Server 2012+, SQLite)
SELECT first_name || ' ' || last_name AS full_name FROM users;

-- NULL propagation: NULL || anything = NULL
SELECT 'Hello' || NULL;  -- → NULL

-- CONCAT() function: NULLs treated as empty strings
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
-- If first_name IS NULL → ' Smith' (NULL coerced to '')

-- MySQL also has CONCAT_WS (with separator — skips NULLs)
SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name;
-- skips NULL middle_name without leaving a double space

Rule of thumb: use CONCAT_WS when joining fields that may be NULL and you do not want extra separators. Use || in Postgres for simple concatenation; use CONCAT() in MySQL-compatible code.

SUBSTRING (or SUBSTR) extracts a portion of a string by position and optional length.

-- Standard SQL: SUBSTRING(string FROM start FOR length)
SELECT SUBSTRING('Hello World' FROM 7 FOR 5);   -- → 'World'

-- Shorthand (all databases):
SELECT SUBSTRING('Hello World', 7, 5);           -- → 'World'
SELECT SUBSTR('Hello World', 7, 5);              -- → 'World' (MySQL, Postgres)

-- Extract from end: use negative position in MySQL
SELECT SUBSTRING('Hello World', -5);             -- → 'World' (MySQL only)

-- Postgres: RIGHT / LEFT shortcuts
SELECT LEFT('Hello World', 5);   -- → 'Hello'
SELECT RIGHT('Hello World', 5);  -- → 'World'

-- Regex-based extraction (Postgres)
SELECT SUBSTRING('Order #12345' FROM '[0-9]+');  -- → '12345'

Rule of thumb: use LEFT(str, n) and RIGHT(str, n) for simple prefix/suffix extraction — they are clearer than SUBSTRING. Use the regex form of SUBSTRING in Postgres for pattern-based extraction.

UPPER() and LOWER() convert all characters in a string to upper or lower case. INITCAP() (Postgres, Oracle) title-cases each word.

SELECT UPPER('hello world');    -- → 'HELLO WORLD'
SELECT LOWER('HELLO WORLD');    -- → 'hello world'
SELECT INITCAP('hello world');  -- → 'Hello World'  (Postgres/Oracle)

-- Common use: case-insensitive comparison
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
-- Better: create a functional index so this is fast:
-- CREATE INDEX idx_users_email_lower ON users (LOWER(email));

Rule of thumb: for case-insensitive searches, use LOWER() with a matching functional index rather than ILIKE (Postgres) on large tables, because LOWER(col) = value can be indexed but col ILIKE value typically cannot.

TRIM, LTRIM, and RTRIM remove characters (default: spaces) from the start, end, or both ends of a string.

SELECT TRIM('  hello  ');           -- → 'hello'
SELECT LTRIM('  hello  ');          -- → 'hello  '
SELECT RTRIM('  hello  ');          -- → '  hello'

-- Remove specific characters (Postgres / SQL Server)
SELECT TRIM(BOTH '.' FROM '...hello...');   -- → 'hello'
SELECT TRIM(LEADING '0' FROM '00042');       -- → '42'

-- MySQL TRIM with specific characters
SELECT TRIM(LEADING '0' FROM '00042');       -- → '42'

Rule of thumb: always TRIM() user-supplied input before storing it in the database. Trailing spaces cause subtle equality failures and bloat CHAR(n) columns.

REPLACE(source, from, to) substitutes all occurrences of from with to in source. The replacement is case-sensitive in most databases.

SELECT REPLACE('Hello World', 'World', 'SQL');   -- → 'Hello SQL'
SELECT REPLACE('aababab', 'ab', 'X');            -- → 'aXXX'

-- Common use: sanitise data
UPDATE products SET sku = REPLACE(sku, '-', '');  -- remove dashes from SKUs

-- Postgres: regexp_replace for pattern-based replacement
SELECT regexp_replace('Order #12345', '[0-9]+', 'XXXXXX');  -- → 'Order #XXXXXX'
SELECT regexp_replace('a1b2c3', '[0-9]', '#', 'g');         -- → 'a#b#c#'
-- 'g' flag = replace all occurrences (global)

Rule of thumb: use REPLACE for literal string swaps; use regexp_replace (Postgres) or REGEXP_REPLACE (MySQL 8+) when the pattern to replace requires a regular expression.

LENGTH and CHAR_LENGTH return the number of characters in a string. OCTET_LENGTH returns the byte count (differs for multi-byte UTF-8).

SELECT LENGTH('Hello');          -- → 5
SELECT CHAR_LENGTH('Hello');     -- → 5 (standard SQL; MySQL synonym)
SELECT LENGTH('こんにちは');      -- Postgres: 5 chars; MySQL: 15 bytes!

-- Postgres: char vs byte length
SELECT char_length('こんにちは');  -- → 5 (characters)
SELECT octet_length('こんにちは'); -- → 15 (bytes in UTF-8)

-- Practical use: enforce a max length
SELECT * FROM users WHERE LENGTH(username) > 50;

Rule of thumb: in MySQL, LENGTH() returns byte count for multibyte strings — use CHAR_LENGTH() to count characters. In Postgres, LENGTH() returns character count.

POSITION, CHARINDEX, INSTR, and STRPOS all find the starting position of a substring (returning 0 or NULL if not found, depending on the database).

-- ANSI standard
SELECT POSITION('World' IN 'Hello World');    -- → 7

-- Postgres
SELECT STRPOS('Hello World', 'World');         -- → 7

-- MySQL
SELECT INSTR('Hello World', 'World');          -- → 7
SELECT LOCATE('World', 'Hello World');         -- → 7
SELECT LOCATE('World', 'Hello World', 8);      -- → 0 (start search at pos 8)

-- SQL Server
SELECT CHARINDEX('World', 'Hello World');      -- → 7

-- Check existence: returns 0 if not found
SELECT * FROM products WHERE POSITION('PRO' IN UPPER(sku)) > 0;
-- Often better expressed as:
SELECT * FROM products WHERE sku ILIKE '%PRO%';  -- Postgres

Rule of thumb: use LIKE or ILIKE for simple contains-checks — they are cleaner and the optimizer understands them. Use position functions when you need the actual offset or want to split on a delimiter.

ROUND, CEIL/CEILING, and FLOOR control numeric rounding.

SELECT ROUND(3.14159, 2);    -- → 3.14
SELECT ROUND(3.145, 2);      -- → 3.15 (or 3.14 — depends on type/rounding mode)
SELECT ROUND(3.5);           -- → 4
SELECT ROUND(-3.5);          -- → -4 (Postgres); -3 (some databases)

SELECT CEIL(3.2);            -- → 4  (round up to nearest integer)
SELECT CEILING(3.2);         -- → 4  (SQL Server alias)
SELECT FLOOR(3.8);           -- → 3  (round down to nearest integer)

-- Truncate without rounding (Postgres)
SELECT TRUNC(3.999, 1);      -- → 3.9
SELECT TRUNC(-3.999, 1);     -- → -3.9 (towards zero, not floor)

-- Practical: round to 2 decimal places for currency display
SELECT ROUND(SUM(total), 2) AS revenue FROM orders;

Rule of thumb: use ROUND(x, 2) for display formatting of monetary values. Use TRUNC (not FLOOR) when you need to drop fractional parts without rounding, especially for negative numbers.

-- MOD: remainder after integer division
SELECT MOD(10, 3);      -- → 1
SELECT 10 % 3;          -- → 1 (Postgres, SQL Server, MySQL)
-- Common use: every Nth row, parity checks
SELECT id FROM orders WHERE MOD(id, 2) = 0;  -- even IDs

-- ABS: absolute value (removes sign)
SELECT ABS(-42);        -- → 42
SELECT ABS(-3.14);      -- → 3.14
-- Use: distance calculations, deviation from target
SELECT product_id, ABS(actual_stock - expected_stock) AS discrepancy
FROM inventory_check;

-- POWER: exponentiation
SELECT POWER(2, 10);    -- → 1024
SELECT 2 ^ 10;          -- → 1024 (Postgres)
-- Use: compound interest, exponential growth models
SELECT principal * POWER(1 + rate, years) AS future_value
FROM loans;

Rule of thumb: prefer the operator form (%, ^) in Postgres for brevity; use MOD() and POWER() for cross-database portability.

Type conversion is done with CAST (standard), :: (Postgres shorthand), or CONVERT (MySQL/SQL Server).

-- Standard SQL CAST
SELECT CAST('42' AS INTEGER);              -- string → integer
SELECT CAST(3.14 AS TEXT);                 -- number → string
SELECT CAST('2026-06-20' AS DATE);         -- string → date

-- Postgres shorthand
SELECT '42'::INTEGER;
SELECT 3.14::TEXT;
SELECT '2026-06-20'::DATE;

-- MySQL CONVERT
SELECT CONVERT('42', UNSIGNED);
SELECT CONVERT(3.14, CHAR);

-- SQL Server CONVERT (also supports format style codes)
SELECT CONVERT(INT, '42');
SELECT CONVERT(VARCHAR, GETDATE(), 103);   -- → '20/06/2026' (UK format)

-- Safe cast (returns NULL instead of error on failure — Postgres 14+)
SELECT pg_catalog.pg_safe_cast('abc', 'integer');  -- → NULL, no error
-- MySQL: CAST('abc' AS UNSIGNED) → 0 (silent coercion, not NULL)

Rule of thumb: use CAST(x AS type) for portability. In Postgres, ::type is idiomatic. Always validate input before casting to avoid runtime errors; use TRY_CAST (SQL Server) or TRY_CONVERT to return NULL on failure instead of raising an exception.

LPAD and RPAD pad a string to a target length with a specified fill character (defaulting to spaces).

SELECT LPAD('42', 5, '0');     -- → '00042'  (zero-pad a number)
SELECT RPAD('hello', 8, '.');  -- → 'hello...'
SELECT LPAD('hello', 3, ' ');  -- → 'hel'  (truncates if longer!)

-- Common use: format fixed-width codes
SELECT LPAD(CAST(id AS TEXT), 8, '0') AS padded_id FROM orders;
-- id=42 → '00000042'

-- Postgres alternative for numeric zero-padding
SELECT TO_CHAR(42, 'FM00000000');  -- → '00000042'

Rule of thumb: use LPAD(value::text, width, '0') for zero-padding integers when formatting export files or codes. Note that LPAD truncates from the left if the string is already longer than the target — always confirm the max width before using it.

TO_CHAR (Postgres, Oracle) and FORMAT (MySQL, SQL Server) convert numbers and dates to formatted strings.

-- Postgres TO_CHAR for numbers
SELECT TO_CHAR(1234567.89, 'FM$999,999,990.00');  -- → '$1,234,567.89'
SELECT TO_CHAR(0.153, 'FM90.0%');                  -- → '15.3%'

-- Postgres TO_CHAR for dates
SELECT TO_CHAR(now(), 'YYYY-MM-DD HH24:MI:SS');   -- → '2026-06-20 14:30:00'
SELECT TO_CHAR(now(), 'Day, DD Month YYYY');       -- → 'Saturday, 20 June 2026'

-- MySQL FORMAT for numbers
SELECT FORMAT(1234567.89, 2);   -- → '1,234,567.89' (locale-aware)

-- SQL Server FORMAT
SELECT FORMAT(1234567.89, 'N2');            -- → '1,234,567.89'
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd');     -- → '2026-06-20'

Rule of thumb: format values for display in the application layer when possible — SQL formatting functions are less testable and locale-handling varies. Use TO_CHAR in SQL when formatting is needed in the query itself (e.g., CSV exports, stored reports).

Splitting a delimited string (e.g., 'a,b,c') into rows requires database-specific functions.

-- Postgres: STRING_TO_ARRAY + UNNEST
SELECT UNNEST(STRING_TO_ARRAY('a,b,c', ',')) AS item;
-- → rows: 'a', 'b', 'c'

-- Postgres: regexp_split_to_table
SELECT regexp_split_to_table('one two three', '\s+') AS word;

-- MySQL 8+: JSON_TABLE workaround (no native split)
SELECT jt.item
FROM JSON_TABLE(
  CONCAT('["', REPLACE('a,b,c', ',', '","'), '"]'),
  '$[*]' COLUMNS (item VARCHAR(100) PATH '$')
) AS jt;

-- SQL Server: STRING_SPLIT (SQL Server 2016+)
SELECT value AS item FROM STRING_SPLIT('a,b,c', ',');

Rule of thumb: storing comma-separated values in a single column is a 1NF violation — use a child table instead. When you must parse a legacy string, UNNEST(STRING_TO_ARRAY(...)) in Postgres is the cleanest approach.

STRING_AGG (Postgres 9.0+, SQL Server 2017+) and GROUP_CONCAT (MySQL) concatenate values from multiple rows into one string, with a separator.

-- Postgres / SQL Server
SELECT customer_id,
       STRING_AGG(product_name, ', ' ORDER BY product_name) AS products
FROM   order_items
GROUP  BY customer_id;
-- → customer_id=1: 'Pen, Pencil, Ruler'

-- MySQL
SELECT customer_id,
       GROUP_CONCAT(product_name ORDER BY product_name SEPARATOR ', ') AS products
FROM   order_items
GROUP  BY customer_id;

-- With DISTINCT to deduplicate
SELECT STRING_AGG(DISTINCT tag, ', ') FROM article_tags WHERE article_id = 1;

Rule of thumb: use STRING_AGG to build comma-separated lists for reports or JSON responses without a second round-trip. Be mindful of the result length — GROUP_CONCAT in MySQL defaults to a 1 024-byte limit (configurable via group_concat_max_len).

Most databases support regex-based filtering and extraction, though the syntax differs.

-- Postgres: ~ (match), !~ (no match), ~* (case-insensitive)
SELECT * FROM users WHERE email ~ '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$';
-- Extract a substring matching a pattern
SELECT REGEXP_MATCH(description, '\d+') AS first_number FROM products;
-- Replace with regex
SELECT REGEXP_REPLACE(phone, '[^0-9]', '', 'g') AS digits_only FROM users;

-- MySQL: REGEXP / RLIKE (filter), REGEXP_REPLACE, REGEXP_SUBSTR (8.0+)
SELECT * FROM users WHERE email REGEXP '^[a-z0-9._%+-]+@';
SELECT REGEXP_REPLACE(phone, '[^0-9]', '') FROM users;

-- SQL Server: no built-in regex; use LIKE for simple patterns
-- or CLR functions / JSON PATH for complex cases
SELECT * FROM users WHERE email LIKE '%@%.%';

Rule of thumb: use LIKE for simple prefix/suffix/contains patterns — it is portable and index-friendly (leading wildcards aside). Use regex functions only when the pattern is too complex for LIKE, and only in Postgres or MySQL where support is solid.