String Functions

Scalar Functions vs Aggregates

From here on, this chapter shifts from aggregate functions (which collapse many rows into one value) to scalar functions — functions applied to a single value, once per row, that produce a transformed value per row. String functions manipulate text data.

CONCAT — Joining Strings Together

sql
SELECT CONCAT(customer_name, ' - ', status) AS summary FROM orders;

Result (for the first two rows):

summary
------------------
Alice - completed
Bob - pending

Dialect variance: CONCAT(a, b, c) (the function form) works in MySQL, PostgreSQL, SQL Server (2012+), and Oracle. Some dialects — notably PostgreSQL, Oracle, and SQLite — also support (or exclusively prefer historically) the || concatenation operator:

sql
SELECT customer_name || ' - ' || status AS summary FROM orders;   -- PostgreSQL / Oracle / SQLite

SQL Server historically used + for string concatenation instead of ||.

UPPER / LOWER — Case Conversion

sql
SELECT UPPER(customer_name) FROM orders WHERE id = 1;   -- 'ALICE'
SELECT LOWER(status)        FROM orders WHERE id = 1;   -- 'completed'

LENGTH — String Length

sql
SELECT LENGTH(customer_name) FROM orders WHERE customer_name = 'Charlie';
-- 7

Dialect variance: LENGTH() is standard/PostgreSQL/MySQL/SQLite; SQL Server uses LEN() instead.

SUBSTRING — Extracting Part of a String

sql
SELECT SUBSTRING(customer_name, 1, 3) FROM orders WHERE customer_name = 'Charlie';
-- 'Cha'   (3 characters starting at position 1)

Signature: SUBSTRING(string, start_position, length). SQL Server also supports SUBSTRING() with this same signature; MySQL additionally accepts SUBSTR() as a common alias.

TRIM — Removing Leading/Trailing Whitespace

sql
SELECT TRIM('  Alice  ') AS cleaned;
-- 'Alice'

TRIM removes leading and trailing spaces by default; most dialects also support LTRIM()/RTRIM() for one-sided trimming, and an optional character argument (TRIM('x' FROM 'xxAlicexx') → 'Alice') to strip characters other than spaces.

REPLACE — Substituting Text

sql
SELECT REPLACE(status, 'completed', 'DONE') FROM orders WHERE id = 1;
-- 'DONE'

REPLACE(string, search_value, replacement) swaps every occurrence of search_value with replacement.

Combining String Functions

sql
SELECT UPPER(SUBSTRING(customer_name, 1, 1)) AS initial FROM orders;
-- 'A', 'B', 'A', 'C', 'B'  (first initial, capitalized)

Edge Cases

• CONCAT() with a NULL argument: in MySQL, CONCAT('a', NULL, 'b') returns NULL for the whole expression (any NULL "poisons" the result) — but PostgreSQL's CONCAT() function specifically treats NULL as an empty string, while its || operator returns NULL if either side is NULL. Always test your specific engine's NULL behavior.
• SUBSTRING with an out-of-range start position or length returns an empty string or truncates gracefully rather than erroring, in most dialects.
• LENGTH('') returns 0; LENGTH(NULL) returns NULL.

Key Takeaways / Interview Q&A

Q: What's the difference between CONCAT() and ||? A: CONCAT() is a function available across most dialects; || is an operator used in PostgreSQL, Oracle, and SQLite. SQL Server historically used + instead.

Q: Which dialect uses LEN() instead of LENGTH()? A: SQL Server.

Q: What does TRIM() do by default? A: Removes leading and trailing spaces from a string (some dialects allow trimming other characters).

Q: Does CONCAT() always treat NULL the same way across dialects? A: No — this varies by dialect (e.g. MySQL's CONCAT propagates NULL, PostgreSQL's CONCAT treats NULL as empty string), so behavior must be verified per engine.