Select column, if blank select from another

sql

nullif

coalesce

custom-functions

byAnton Shumikhin·Jan 16, 2025

To select the first non-blank column value, pair COALESCE with NULLIF targeting empty strings, or opt for a CASE statement for scenarios requiring more complex condition handling.

-- Because sometimes the best choice is the second one 😉
SELECT COALESCE(NULLIF(first_column, ''), second_column) AS result_column FROM your_table;

In cases requiring more layered logic, deploy a CASE:

-- A backup plan is always a good plan, right? 🤷‍♀️
SELECT CASE WHEN first_column <> '' THEN first_column ELSE second_column END AS result_column FROM your_table;

Deep Dive: COALESCE and NULLIF

COALESCE, a SQL standard function, gives the first non-null argument. Married with NULLIF, which offers NULL when two expressions are identical, we effectively interpret a blank string as NULL.

Smart Handling: CASE Statement for Whitespace

In situations where there are more complex conditions involving multiple columns, the CASE statement flexes its muscles. For instance, you might want to replace a field only when it's blank or containing whitespace:

-- Who likes extra spaces, anyway? 🚀
SELECT CASE 
  WHEN LEN(TRIM(first_column)) = 0 THEN second_column 
  ELSE first_column 
END AS result_column 
FROM your_table;

SQL Behavior: ANSI_NULLS and QUOTED_IDENTIFIER

The combination of COALESCE and NULLIF is elegant, but do remember certain database settings can impact results. If you're toying with custom functions in SQL Server, check ANSI_NULLS and QUOTED_IDENTIFIER settings.

Performance vs Readability: Custom Functions

Developing custom functions for checking blank fields can improve code readability, but beware the potential performance impact - each function call adds a little overhead. Remember to balance benefits and potential performance costs.

explain-codes / Sql / Select column, if blank select from another

Linked

How to check if a field is null or empty in MySQL?



How to use NULL or empty string in SQL



Mysql: Typecasting NULL to 0



How do I check if a column is empty or null in MySQL?



T-sql Substring - Last 3 Characters

