How to replace a NULL value in SQL table?

Question

I have a table dbo.People in a database and one of the columns is Name. Some of the fields however are missing a Name value. So I am trying to replace the empty field with No Name. There is also an ID column with values 1, 2, 3, 4, and so on.

What I have so far:

SELECT ISNULL(Name, 'No Name') FROM dbo.People;

I then want to group and count how many times that name occurs

SELECT COUNT(ID), Name
FROM dbo.People
GROUP BY Name;

This works, however my result still shows a blank value with how many times that blank value occurs, rather than No Name.

you didn't use the ISNULL(Name, 'No Name' ) in your second query at all — Squirrel
– Squirrel, Commented Sep 14, 2018 at 7:09
Simply selecting on your first query doesn't change the underlying data at all. It only changes how you see the results after you run that SELECT. You need to use UPDATE if you want it changed forever, or include the ISNULL again on your 2nd query. — CurseStacker
– CurseStacker, Commented Sep 14, 2018 at 7:11
ISNULL() is not update NULL values. It just display as No Name.. — Pugal
– Pugal, Commented Sep 14, 2018 at 7:11
Your question aside, I can tell you you're probably making more trouble for yourself than if you left the names null (or even an empty string would be better, if you're not a fan of nulls). I've run across many a data set that contains things like -32768 for nulls or NaN when a null indicates that universally. — Xedni
– Xedni, Commented Sep 14, 2018 at 7:19

Ari · Accepted Answer · 2018-09-14 07:32:02Z

4

I realised thanks to Squirrel and other commenters that the way I was typing the query was making it two separate queries.

The correct way I found was to combine them into one:

SELECT COUNT(ID), ISNULL(Name, 'No Name')
FROM dbo.People
GROUP BY Name;

edited Sep 14, 2018 at 7:32

answered Sep 14, 2018 at 7:23

Ari

6,2899 gold badges66 silver badges146 bronze badges

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

Comments

Zaynul Abadin Tuhin · Accepted Answer · 2018-09-14 07:12:33Z

3

use case when

select sum(case when name='No Name' then 1 else 0 end) as cnt,name from
(
 SELECT coalesce(Name, 'No Name' ) as name
 FROM dbo.People
) t group by name

answered Sep 14, 2018 at 7:12

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Comments

user1694674 · Accepted Answer · 2018-09-14 08:10:49Z

0

Do you only want to get the NULL occurences?

No need to overcomplicate things then, just remember that you cannot compare against a lack of value/data but you can check for it.

SELECT Count(ID) 
FROM dbo.People 
WHERE Name IS NULL

answered Sep 14, 2018 at 8:10

user1694674

1265 bronze badges

Collectives™ on Stack Overflow

How to replace a NULL value in SQL table?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related