--- title: "09 — String, Conditional, and Null-Handling Functions" uid: string-and-conditional-functions tags: ["sql", "coalesce", "roadmap:sql", "replace", "concat", "lower", "upper", "nullif", "substring", "case"] excerpt: "String and conditional functions reshape values row-by-row inside SELECT — and CASE, COALESCE, and NULLIF bring conditional logic and null-safety to the same place." date: 2026-08-13T03:27:37+0000 source: https://www.aveshina.my.id/en/blog/string-and-conditional-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. ```figure ``` ## 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/](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/](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](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](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/](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](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/](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/](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](https://www.youtube.com/watch?v=Jaw53T__RRY) ```quiz Q: What does COALESCE(a, b, c) return? - The first non-null value among a, b, c - The last value among a, b, c correct: 0 explain: COALESCE walks its arguments left to right and returns the first that isn't NULL, making it the idiomatic way to supply a default. Q: How do you prevent a divide-by-zero error when computing SUM(x) / COUNT(y)? - Wrap the denominator in NULLIF(COUNT(y), 0) so it becomes NULL instead of erroring - Use CEILING on the denominator correct: 0 explain: NULLIF(count, 0) returns NULL when count is 0, and dividing by NULL yields NULL (no crash) instead of raising an error. Q: CASE in a SELECT list is used to… - return a different value per row based on conditions (like if/else) - sort the result set correct: 0 explain: CASE evaluates WHEN conditions per row and returns the matching result, letting you derive categorized columns directly in the query. Q: Which pair of functions forces case-insensitive comparison on the WHERE clause? - UPPER/LOWER applied to both sides - CONCAT/LENGTH correct: 0 explain: WHERE LOWER(email) = LOWER(input) compares both sides in the same case, giving case-insensitive matching. Q: Why might the same string function behave differently across Postgres and SQL Server? - function names and argument conventions vary by dialect (e.g., LENGTH vs LEN) - string functions are standardized and identical everywhere correct: 0 explain: The concepts are portable but exact names and signatures drift between dialects, so cross-engine queries need a dialect check. ```