How to check a column values is null or not in select statement

Question

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?

Answer 1

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.