Question 1

Normalizing email case for lookups

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 2

Where does SQL's index start?

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

Counting characters in a literal

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

Swapping every matching token

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 5

Joining name columns with a space

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

What happens to the surrounding spaces?

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.

Question 11

Locating a character in an email address

Accepted Answer

POSITION(substring IN string) returns the 1-based position of the first occurrence of substring, or 0 if not found. 'user@example.com': u=1, s=2, e=3, r=4, @=5. The @ sign is at position 5. PostgreSQL also accepts STRPOS(string, substring) with the arguments reversed.

Question 12

Slicing from each end of a string

Accepted Answer

LEFT(string, n) returns the first n characters; RIGHT(string, n) returns the last n characters. LEFT('abcdef', 3) = 'abc' (first 3). RIGHT('abcdef', 2) = 'ef' (last 2). These are shorthand for SUBSTRING - LEFT(s, n) is equivalent to SUBSTRING(s, 1, n).

Question 13

Concatenation with NULL

Accepted Answer

In SQL, concatenating anything with NULL using || produces NULL. NULL propagates through the expression. This trips up developers who expect NULL to behave like an empty string. Fix: use COALESCE to substitute a default - 'Hello' || COALESCE(NULL, '') || 'World' returns 'HelloWorld'.

Question 14

POSITION when substring is absent

Accepted Answer

When the search substring is not found, POSITION returns 0 - not NULL and not -1. This differs from many programming languages that return -1 for 'not found'. You can use this in a WHERE clause: WHERE POSITION('@' IN email) > 0 to find rows that contain an '@' character.

Question 15

Case-insensitive LIKE search

Accepted Answer

Standard LIKE is case-sensitive in PostgreSQL. The portable approach is to normalize both sides: LOWER(name) LIKE '%alice%'. PostgreSQL also has ILIKE for case-insensitive LIKE: WHERE name ILIKE '%alice%'. MySQL LIKE is case-insensitive by default on case-insensitive collations. Know your database's behavior.

String Functions