Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Replacing NULL and empty string within Select statement

Tags:

sql

tsql

I have a column that can have either NULL or empty space (i.e. '') values. I would like to replace both of those values with a valid value like 'UNKNOWN'. The various solutions I have found suggest modifying the value within the table itself. However, this is not an option in this case in that the database is for a 3rd party application that is developed and/or patched very poorly (in reality I think my Rottweiler could have done a better job). I am concerned that modifying the underlying data could cause the application to melt into a smoking hole.

I have attempted variations of the following commands:

COALESCE(Address.COUNTRY, 'United States') -- Won't replace empty string as it is not NULL
REPLACE(Address.COUNTRY, '', 'United States') -- Doesn't replace empty string
ISNULL(Address.COUNTRY, 'United States') -- Works for NULL but not empty string

I know I could use the CASE statement but am hoping there is a much more elegant/efficient solution.

You'll have to trust me when I say I have looked for a solution to my specific issue and have not found an answer. If I have overlooked something, though, kindly show me the lighted path.

like image 267
mbadtk Avatar asked Jun 21 '13 21:06

mbadtk


3 Answers

Try this

COALESCE(NULLIF(Address.COUNTRY,''), 'United States')
like image 165
rs. Avatar answered Nov 08 '22 01:11

rs.


Sounds like you want a view instead of altering actual table data.

Coalesce(NullIf(rtrim(Address.Country),''),'United States')

This will force your column to be null if it is actually an empty string (or blank string) and then the coalesce will have a null to work with.

like image 5
Brad Avatar answered Nov 07 '22 23:11

Brad


An alternative way can be this: - recommended as using just one expression -

case when address.country <> '' then address.country
else 'United States'
end as country

Note: Result of checking null by <> operator will return false.
And as documented: NULLIF is equivalent to a searched CASE expression
and COALESCE expression is a syntactic shortcut for the CASE expression.
So, combination of those are using two time of case expression.

like image 3
shA.t Avatar answered Nov 08 '22 01:11

shA.t