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.
Try this
COALESCE(NULLIF(Address.COUNTRY,''), 'United States')
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.
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 returnfalse
.
And as documented:NULLIF
is equivalent to a searchedCASE
expression
andCOALESCE
expression is a syntactic shortcut for theCASE
expression.
So, combination of those are using two time ofcase
expression.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With