how to replace blank & NULL in SELECT

Question

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

Please tag DB engine that you use

Leszek Mazur
– Leszek Mazur

2020-01-27 14:41:44 +00:00
Commented Jan 27, 2020 at 14:41 — Leszek Mazur
– Leszek Mazur, Commented Jan 27, 2020 at 14:41

Gordon Linoff · Accepted Answer · 2020-01-27 13:52:06Z

3

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

answered Jan 27, 2020 at 13:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

how to replace blank & NULL in SELECT

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related