CODEWITHSUNDEEP
sqlt-sql
mbadtk
mbadtk

Reputation: 293

Replacing NULL and empty string within Select statement

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.

Upvotes: 27

Views: 147143

Answers (4)

Ankit Bharti
Ankit Bharti

Reputation: 87

For an example data in your table such as combinations of

'', null and as well as actual value than if you want to only actual value and replace to '' and null value by # symbol than execute this query

SELECT Column_Name = (CASE WHEN (Column_Name IS NULL OR Column_Name = '') THEN '#' ELSE Column_Name END) FROM Table_Name

and another way you can use it but this is little bit lengthy and instead of this you can also use IsNull function but here only i am mentioning IIF function

SELECT IIF(Column_Name IS NULL, '#', Column_Name) FROM Table_Name  
SELECT IIF(Column_Name  = '', '#', Column_Name) FROM Table_Name  
-- and syntax of this query
SELECT IIF(Column_Name IS NULL, 'True Value', 'False Value') FROM Table_Name

Upvotes: 2

shA.t
shA.t

Reputation: 16958

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.

Upvotes: 3

Brad
Brad

Reputation: 12245

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.

Upvotes: 8

rs.
rs.

Reputation: 27427

Try this

COALESCE(NULLIF(Address.COUNTRY,''), 'United States')

Upvotes: 80

Related Questions