Replacing Null Entries with a Zero

Question

I've been working on learning SQL starting with basically no knowledge. I've only been at this for about a week.
I'm working with a piece of simulation software. I've been given a task to run many replications of a simulation that will generate random events of either something happens or nothing happens. The sim will create a table of the events and a column for replication numbers. If the event happened then it will get an event number, if it didn't, it will skip that rep and go to the next. I need a query to take the replications that have no event and replace the null entry with a zero.

As an added bonus, I might need to generate the number for the replication where there is a null.

The last bit of code I tried looked like this:

SELECT IsNull(table1.column1, 0)
FROM table1

That looks ok to me. were you getting an error? Or weird results? — Ryan B.
– Ryan B., Commented Nov 7, 2014 at 21:46
Check to make sure the table actually contains nulls, sometimes especially if the data was imported from Excel, the field contains the word 'null' not a null value. — HLGEM
– HLGEM, Commented Nov 7, 2014 at 21:49
Found out that the issue was on the person giving the challenge. They forgot that first of all, the table didn't return null values because the entries that they were expecting to return the null values had never been created. So the solution (which wasn't even possible for what we were using) was to create a new table with numbers 1-n and then left join them together. — JohnN
– JohnN, Commented Nov 11, 2014 at 17:32

Mat Richardson · Accepted Answer · 2014-11-07 21:47:02Z

2

SELECT COALESCE(table1.column1,0) 
FROM table1

Should do the trick. COALESCE returns the first non-null value it finds, so this should work.

answered Nov 7, 2014 at 21:47

Mat Richardson

3,5985 gold badges37 silver badges57 bronze badges

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

1 Comment

Daniel Renshaw Over a year ago

In the OP's case ISNULL(X, 0) should work fine. The differences between ISNULL and COALESCE are enumerated here: msdn.microsoft.com/en-us/library/ms190349.aspx

Collectives™ on Stack Overflow

Replacing Null Entries with a Zero

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related