How to get rid of NULL values in SQL Server 2008 table

Question

I have a table like:

1   0.22    0.14    0.05
23  11      0.32    0.16
4   NULL    1       0.39
NULL NULL   .8      1
NULL .5     .7      NULL

I want to modify the table to become

1   0.22  0.14  0.05
23   1    0.32  0.16
4   -1      1   0.39
-1  -1     .8     1
-1  .5     .7    -1

If you look I changed the NULL by -1

I was trying:

SELECT
  coalesce([a1], -1), coalesce([a2], -1),coalesce([a3], -1), coalesce([a4], -1)
FROM tableee;

Which is correct, but when I do a SELECT * I get the table with null values...

How do I modify the table so when I do a SELECT * I do not get NULL but -1 instead?

Your "fix" is just a SELECT statement. Why would you expect that to impact subsequent queries? Shouldn't you be updating your tables? — Kirk Woll
– Kirk Woll, Commented Sep 2, 2011 at 18:19
Do you want to edit the table contents? or just the resulting query? — AJC
– AJC, Commented Sep 2, 2011 at 18:20

Abe Miessler · Accepted Answer · 2011-09-02 18:19:43Z

7

If you want to change the values in the table you need to update the table:

UPDATE Tableeee t
SET t.a1 = COALESCE(t.a1,-1),
    t.a2 = COALESCE(t.a2,-1),
    t.a3 = COALESCE(t.a3,-1)
    t.a4 = COALESCE(t.a4,-1)

Or you can change the columns to not nullable and give a default value of -1. You will probably want to do this if you have that requirement. Updating the data would just be a bandaide, you need to enforce this requirement in the DB.

answered Sep 2, 2011 at 18:19

Abe Miessler

85.7k104 gold badges323 silver badges496 bronze badges

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

Comments

cdhowie · Accepted Answer · 2011-09-02 18:20:59Z

1

You could change the table schema so that the columns are non-nullable, and have a default value of -1. That should alter the data so that all of the NULL values become -1.

If you don't want to actually change the data but only the data set returned by the query, consider creating a view based off of your COALESCE()ing query. Then you can use that view whenever you want to see -1 in place of NULL.

answered Sep 2, 2011 at 18:20

cdhowie

172k25 gold badges303 silver badges324 bronze badges

Comments

Jennifer S · Accepted Answer · 2011-09-02 19:19:49Z

1

There is no way to actually modify the table so that you get -1 instead of null with a select *. You are allowing nulls for those columns so it is not desirable logic to hide the nulls.

Now that being said, you have three options:

1) Update the table to not allow nulls in those columns and then set the default to -1 (or use a trigger) note: This requires admin priviledges, which you may not have.
2) Always do a select with your coalease or an isnull() test
3) You could make a view of the same table and then in the view select do your coalesce() or isnull() logic, then you could just select * from the view, and it would give you the same table but with the null changed out.

Just FYI, option 2 or 3 has overhead, so only do this if #1 is not an option.

edited Sep 2, 2011 at 19:19

Jennifer S

1,4492 gold badges26 silver badges45 bronze badges

answered Sep 2, 2011 at 18:22

ElementZero

3582 silver badges10 bronze badges

2 Comments

ElementZero Over a year ago

I like how every other comment has the same suggestion as I gave for #1 and yet you down rate only mine...? And why down rate anyways when it is a valid (and not to mention the correct) option. Sure, he might not have DB admin privilege, but I did say perform 2 or 3 if #1 was not an option after all. Just trying to understand...

John Sansom Over a year ago

+1: Agreed. If you're going to down vote and it's not clear why then clarify your decision with a comment so we may all learn from it. The Answer is actually perfectly good in my opinion (for the most part as modify rights would be required to Update, not Admin) . The answer actually looks at the wider issue too, always a good thing to do.

Collectives™ on Stack Overflow

How to get rid of NULL values in SQL Server 2008 table

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related