I am new to SQL Server, and I want to validate whether a column value is NULL or empty in the select statement .
The select statement is :
SELECT COM_TYPE, COM_IN_CHARGE_POSITION
FROM TABLE_01
If the COM_TYPE value is null or empty, I want to take the COM_TYPE_OTHERS value as COM_TYPE for the same way to COM_IN_CHARGE_POSITION.
It's simply if the COM_TYPE and COM_IN_CHARGE_POSITION is null or empty I want to take the values from other two columns.
Can anyone help me to solve this?
Let me assume that blank is NULL. In that case, you simply want coalesce():
select coalesce(COM_TYPE, COM_TYPE_OTHERS, COM_IN_CHARGE_POSITION, COM_IN_CHARGE_POSITION_OTHERS) as effective_COM_TYPE
The coalesce() function takes the first non-NULL argument. If the values could be empty strings or strings with spaces in addition to NULL, then a case expression is recommended.
COALESCE function can check which one column is first non-NULL, but you, if you want to check, is null or empty you can try to use CASE WHEN
SELECT CASE WHEN COM_TYPE IS NULL OR COM_TYPE = '' THEN COM_TYPE_OTHERS
ELSE COM_TYPE END AS COM_TYPE,
CASE WHEN COM_IN_CHARGE_POSITION IS NULL OR COM_IN_CHARGE_POSITION = '' THEN COM_IN_CHARGE_POSITION_OTHERS
ELSE COM_IN_CHARGE_POSITION END AS COM_IN_CHARGE_POSITION,
FROM TABLE_01
Simplest way to check for null or empty is to use ISNULL and NULLIF :
SELECT ISNULL(NULLIF(COM_TYPE, ''), COM_TYPE_OTHERS)
FROM TABLE_01
You can also use COALESCE but it does not check for empty string (only null), so you will still have to use NULLIF.
Also note that ISNULL is faster than COALESCE even if the difference is pretty negligible.
You can use COALESCE() or ISNULL() OR CASE WHEN statement
SELECT COALESCE( COM_TYPE, COM_TYPE_OTHERS ) AS COM_TYPE,
COALESCE (COM_IN_CHARGE_POSITION , COM_IN_CHARGE_POSITION_OTHERS) AS COM_IN_CHARGE_POSITION
FROM TABLE_01