Question 1

REPLACE all occurrences

Accepted Answer

REPLACE(string, from, to) replaces every non-overlapping occurrence of 'from' with 'to'. Both occurrences of 'foo' are replaced, giving 'baz bar baz'. REPLACE is case-sensitive in most databases - 'FOO' would not be replaced.

Question 2

SUBSTRING extraction

Accepted Answer

SUBSTRING(string, start, length) extracts a substring. The start position is 1-indexed in SQL (not 0-indexed like most programming languages). Starting at position 1 and taking 5 characters gives 'Hello'. PostgreSQL also accepts SUBSTRING('Hello World' FROM 1 FOR 5) - both forms are valid.

Question 3

LENGTH of a string

Accepted Answer

LENGTH returns the number of characters in a string. 'Hello' has 5 characters. In PostgreSQL, LENGTH and CHAR_LENGTH are interchangeable for text. For byte-length of multi-byte UTF-8 strings, use OCTET_LENGTH instead.

Question 4

UPPER and LOWER

Accepted Answer

LOWER converts every character in the string to lowercase. UPPER does the opposite. These are commonly used to normalize data before comparison, for example: WHERE LOWER(email) = 'alice@example.com' for case-insensitive email lookups.

Question 5

String concatenation

Accepted Answer

|| is the standard SQL concatenation operator. It joins the three strings: 'Alice', ' ', 'Smith' into 'Alice Smith'. MySQL uses CONCAT(first_name, ' ', last_name) instead of ||. PostgreSQL supports both. Note: if either side is NULL, || returns NULL. Use COALESCE to handle NULLs: COALESCE(first_name, '') || ' ' || COALESCE(last_name, '').

Question 6

TRIM removes whitespace

Accepted Answer

TRIM removes leading and trailing whitespace (spaces) from a string by default. LTRIM removes only leading whitespace; RTRIM removes only trailing whitespace. TRIM can also remove specific characters: TRIM('x' FROM 'xxhelloxx') returns 'hello'.

Question 7

LENGTH with spaces

Accepted Answer

LENGTH counts every character including spaces. '  hi  ' has 2 leading spaces, 2 characters, and 2 trailing spaces - 6 total. If you want the length without surrounding whitespace, wrap it: LENGTH(TRIM('  hi  ')) returns 2. This is a common source of bugs when data has inconsistent padding.

Question 8

UPPER on mixed case

Accepted Answer

UPPER converts only alphabetic characters to uppercase. Digits and spaces are unaffected - 123 stays as 123. The result is 'HELLO WORLD 123'. You can use UPPER and LOWER together to implement title-casing, though SQL has no built-in INITCAP in all databases (PostgreSQL does have INITCAP).

Question 9

LTRIM vs RTRIM

Accepted Answer

LTRIM removes only the leading (left-side) whitespace and leaves trailing spaces intact. The result is 'hello  ' - two trailing spaces remain. Compare: RTRIM('  hello  ') returns '  hello' (only trailing spaces removed), and TRIM removes both sides. These functions are useful when your data has inconsistent padding on just one side.

Question 10

REPLACE with no match

Accepted Answer

When the search string is not found anywhere in the input, REPLACE returns the original string unchanged. 'xyz' does not appear in 'hello world', so the output is 'hello world'. REPLACE never returns NULL just because there was no match - it only returns NULL if the input string itself is NULL.