Replacing NULL and empty string within Select statement

Question

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.

rs. · Accepted Answer · 2013-06-21 21:50:25Z

80

Try this

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

answered Jun 21, 2013 at 21:50

rs.

27.6k13 gold badges71 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mbadtk Over a year ago

This seems to have done it. Beer's on me. However, I'm surprised that it does work in that my understanding is that the COALESCE function will grab the first non-null value. In the statement I would expect it to return empty space as opposed to the text string??? But, bottom line, thanks for the solution.

rs. Over a year ago

There are two functions in this statement, one NULLIF which is converting empty string to null and then coalesce which checks for null

shA.t Over a year ago

@rs.: Note: nullif doesn't convert empty string to null, It returns null if two arguments are same ;).

Brad · Accepted Answer · 2013-12-31 17:58:26Z

8

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.

edited Dec 31, 2013 at 17:58

answered Jun 21, 2013 at 21:50

Brad

12.4k4 gold badges51 silver badges82 bronze badges

6 Comments

Wolph Over a year ago

Having both ltrim and rtrim seems useless? If it's empty (except spaces) it's empty from either side ;)

Brad Over a year ago

@WoLpH aye, very true. I removed one. ( I guess I just habitually write both)

mbadtk Over a year ago

Creating a view hadn't crossed my mind though it probably should have. rs and your variation provided an adequate solution so no need for view.

Wolph Over a year ago

+1 from me, I think this is the better solution since it covers more cases :)

KatDevsGames Over a year ago

I see 3 opening parentheses and 4 closing ones currently... assuming the first closing parenthesis is a dud.

|

shA.t · Accepted Answer · 2017-09-12 12:34:28Z

3

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.

edited Sep 12, 2017 at 12:34

answered Sep 12, 2017 at 12:28

shA.t

17.1k5 gold badges59 silver badges121 bronze badges

Comments

Ankit Bharti · Accepted Answer · 2019-10-02 07:03:35Z

2

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

edited Oct 2, 2019 at 7:03

answered Oct 2, 2019 at 6:35

Ankit Bharti

876 bronze badges

Collectives™ on Stack Overflow

Replacing NULL and empty string within Select statement

4 Answers 4

3 Comments

6 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related