Replace blank and null values in SQL

Question

I have the following query:

Select 
   impressions,
   COALESCE(u.company,u.email) AS Publisher
FROM users AS u

Here, the company field has a company name or a Null or a Blank value. I don't want Publisher column to have a blank or a null value.

The above statement works for NULL as it returns the u.email value in that case. But it does not work for blank values. So I tried doing:

COALESCE(NULLIF(u.company,''),u.email)

This does not seem to work as blank values are not replaced by email.

Can somebody please point out where I am going wrong? Is there an alternative?

It's uglier...but case when u.company is null then u.email when trim(u.company) = '' then u.email else u.company end — Twelfth
– Twelfth, Commented Jul 20, 2016 at 19:19

Randy · Accepted Answer · 2016-07-20 19:02:21Z

1

maybe this:

COALESCE(NULLIF(u.company,u.email),u.email)

answered Jul 20, 2016 at 19:02

Randy

16.7k2 gold badges40 silver badges56 bronze badges

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

1 Comment

Cerberus Over a year ago

But won't it compare u.company and u.email in this case? I am trying get rid of null and blank values in the company field and replace it with email.

TheDeveloper · Accepted Answer · 2016-07-20 19:08:55Z

1

If I understand what you are saying correctly.

COALESCE evaluates the arguments in order and returns the current value of the first expressions that initially does not evaluate to NULL.

So this would seem to be why your statement works for NULL. In the case of it not working for blank values are because a blank value is still technically a value if evaluated as a string.

Also, if you are referring to the Database table not being able to have a null or blank value you can set a table to not accept a null value by:

ALERT TABLE [Table name] ALTER COLUMN [column] INTEGER NOT NULL

Integer obviously would be replaced if its not of that type. Doing this can trigger problems in code that communicates with the database though if it isnt written properly to handle null values sent to a db.

To replace values in a certain column with values that are in another column such as replacing company values with email values you could try.

UPDATE [table name] t
SET t.[column name] =
(SELECT [other column name from table2] FROM [table2 name] v
WHERE t.[column name] = v.[column name from table2]);

edited Jul 20, 2016 at 19:08

answered Jul 20, 2016 at 19:04

TheDeveloper

627 bronze badges

1 Comment

Cerberus Over a year ago

Your are correct but is there is no way of replacing the blank and null values in company column with email?

Twelfth · Accepted Answer · 2016-07-20 19:50:37Z

1

This is a bit dirtier, but it'll work. Just define each case that you want to replace in a case statement (use trim in case someone put in 2 or 3 spaces). Probably not the optimal solution though

case when u.company is null then u.email 
     when trim(u.company) = '' then u.email 
else u.company 
end

answered Jul 20, 2016 at 19:50

Twelfth

7,2083 gold badges30 silver badges35 bronze badges

1 Comment

Cerberus Over a year ago

Maybe not optimal, but this does make sense!!

Collectives™ on Stack Overflow

Replace blank and null values in SQL

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related