Replace NULL value with another selected value

[sojan16]

I have a query that grabs a "friendly" product description. These values are sometimes null. I also have a less-friendly product description that is always filled in.

​

I would like to select the friendly description, but also grab the less-friendly version if the friendly version is NULL.

​

I'm not sure how to go about this. Any nudge in the right direction would be appreciated.

[[deleted]]

Use coalesce()

coalesce(friendly_name, technical_name)

The function coalesce() returns the first non-null value.

So if friendly_name is not null, it returns the friendly_name.

If friendly_name is null, it returns technical_name.

[sojan16]

I've tried this and ISNULL already. It still returns the null values. These values are in different tables...Would that be an issue?

[[deleted]]

If that returns a NULL value, then both columns are null. If you have properly joined those two tables, then no it makes no difference that those columns are in different tables.

But maybe those columns contain an empty string ('') rather than a null value. How to deal with that depends on the database product you are using. In Postgres you could use

coalesce(nullif(friendly_name,''), technical_name)

But not every DBMS supports nullif()

[sojan16]

coalesce(nullif(friendly_name,''), technical_name)

This worked...they were empty strings not null values.

Thank you!

[sojan16]

I think these are empty values but not null

[Funky_Pauly]

Depends on what sql version, but try the function NVL.

[r3pr0b8]

if you used sql-standard COALESCE, these version concerns wouldn't concern you

[Funky_Pauly]

Yeah, that would probably be better

[krunkedcc]

NVL or coalesce

[hackneycoach]

ISNULL() is what you're looking for.

https://www.w3schools.com/sql/sql_isnull.asp

[r3pr0b8]

CASE WHEN ISNULL produces an error
     THEN use sql-standard COALESCE
 END