09 — String, Conditional, and Null-Handling Functions

Pushing value transformations into application code because I didn't trust SQL to do it cleanly was my habit — and it was mostly misplaced caution. Writing them down produced one model: string and conditional functions transform each row's values inside the SELECT list (or WHERE), and CASE, COALESCE, and NULLIF bring conditional logic and null-safety to exactly the same place. [1]

The framing that clicked is that these functions aren't a special category — they're the same idea as FLOOR or ABS, applied to text and to decisions. Once I treated "transform a value" as a normal part of the SELECT list, I stopped round-tripping data through the app just to uppercase a field or fill a null.

String functions — reshape text per row

The core string functions each do one transformation on a text value [1][2][3][4][5]:

• CONCAT(a, b, ...) — join strings into one. CONCAT(first, ' ', last) → "Ave S".
• LENGTH(s) — number of characters. Note SQL Server calls it LEN; multi-byte sets can surprise you [2].
• SUBSTRING(s, start, length) — slice a portion. SUBSTRING('Ave', 1, 2) → "Av" [3].
• REPLACE(s, old, new) — swap every occurrence of a substring. Data-cleaning workhorse for typos and standardization [4].
• UPPER(s) / LOWER(s) — force case. The standard trick for case-insensitive comparison: WHERE LOWER(email) = LOWER(input).

SELECT UPPER(CONCAT(first_name, ' ', last_name)) AS shouty_name
FROM customers
WHERE SUBSTRING(email, 1, 1) = 'a';

CASE — conditional logic in the query

CASE is the SQL equivalent of if/else, returning a value per row based on conditions [6]. It goes in the SELECT list (to derive a column) or anywhere a value is needed:

SELECT name,
       CASE
         WHEN total > 1000 THEN 'whale'
         WHEN total > 100  THEN 'regular'
         ELSE 'new'
       END AS tier
FROM customers;

Each WHEN is tested in order; the first true one wins; ELSE is the fallback. CASE is how I bucketize continuous values into categories without round-tripping through the application.

COALESCE — the first non-null

COALESCE returns the first non-null value among its arguments [7][8]. It's the idiomatic way to supply a default for missing data:

SELECT name, COALESCE(nickname, name, 'anonymous') AS display_name
FROM customers;

The practical tie-in with aggregates: AVG(COALESCE(spend, 0)) treats nulls as zero, whereas AVG(spend) skips them. Most "the average looks wrong" bugs are this distinction in disguise.

NULLIF — the guard against edge cases

NULLIF(a, b) returns NULL if a = b, otherwise a [9]. Its classic use is preventing division by zero:

SELECT SUM(sales) / NULLIF(COUNT(orders), 0) AS avg_per_order
FROM ...

Without NULLIF, dividing by zero is an error. With it, the result becomes NULL (no average rather than a crash), which downstream code can handle gracefully. NULLIF and COALESCE compose — COALESCE(NULLIF(x, 0), -1) turns a zero into a sentinel.

LENGTH and the per-dialect names

One wrinkle worth flagging: function names drift between dialects [2]. LENGTH is standard; SQL Server uses LEN. SUBSTRING argument conventions vary slightly. The concepts are portable; the exact spelling isn't, so I check the dialect's docs when moving a query between Postgres, MySQL, and SQL Server.

How I use this

The habit I keep is the "where should this transform live?" check. If a transform is about the data (uppercasing an email for comparison, filling a null with a default, bucketizing a number), it belongs in SQL — it travels with the data everywhere, runs once at the source, and keeps the application code free of per-row cleanup. I push string cleanup, COALESCE defaults, and CASE bucketization into the query precisely so that every consumer of that query sees the cleaned value without re-implementing the rule.

References

[1] SQLShack, "An overview of the CONCAT function in SQL with examples," sqlshack.com, 2024. [Online]. Available: https://www.sqlshack.com/an-overview-of-the-concat-function-in-sql-with-examples/

[2] LearnSQL, "How to Check the Length of a String in SQL," learnsql.com, 2024. [Online]. Available: https://learnsql.com/cookbook/how-to-check-the-length-of-a-string-in-sql/

[3] W3Schools, "SQL SUBSTRING," w3schools.com, 2024. [Online]. Available: https://www.w3schools.com/sql/func_sqlserver_substring.asp

[4] DataCamp, "How to use the SQL REPLACE Function," datacamp.com, 2024. [Online]. Available: https://www.datacamp.com/tutorial/sql-replace

[5] LearnSQL, "How to Convert a String to Uppercase in SQL," learnsql.com, 2024. [Online]. Available: https://learnsql.com/cookbook/how-to-convert-a-string-to-uppercase-in-sql/

[6] Mode Analytics, "SQL CASE - Intermediate SQL," mode.com, 2024. [Online]. Available: https://mode.com/sql-tutorial/sql-case

[7] LearnSQL, "How to use the COALESCE function in SQL," learnsql.com, 2024. [Online]. Available: https://learnsql.com/blog/coalesce-function-sql/

[8] PostgreSQL Tutorial, "PostgreSQL COALESCE," postgresqltutorial.com, 2024. [Online]. Available: https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-coalesce/

[9] YouTube, "What is NULLIF in SQL?," 2023. [Online]. Available: https://www.youtube.com/watch?v=Jaw53T__RRY