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

User · Accepted Answer

Try this  COALESCE NULLIF Address COUNTRY       United States

User · Answer

An alternative way can be this  - recommended as using just one expression -  case when address country  lt  gt     then address country else  United States  end as country      Note  Result of checking null by  lt  gt  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

User · Answer

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

User · Answer

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

[sql] Replacing NULL and empty string within Select statement

Examples related to sql

Examples related to tsql