CODEWITHSUNDEEP
sqlsql-server
colorfulmonochrome
colorfulmonochrome

Reputation: 11

how to replace blank & NULL in SELECT

Please let me question about how to replace blank & NULL value in SELECT. I have a column that is defined by VARCHAR like below. It has blank and NULL values in some row. How can I replace both blank and NULL in SELECT?

Replacing NULL with 0 in a SQL server query -> This thread answered replacing only NULL

col1
------------
abc
------------
def
------------
NULL         <<<<<<<<<< NULL
------------
ghi
------------
             <<<<<<<<<< Blank
------------
jkl

Upvotes: 1

Views: 1368

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You can use a case expression or nullif(). Something like this:

select coalesce(nullif(col, ''), <replacement value>) as col

If the logic is more complicated -- say strings of blanks -- then case is simpler:

select (case when col is null or replace(col, ' ') = '' 
             then <replacement value>
             else col
        end) as col

Upvotes: 3

Related Questions