SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Friday, May 19, 2006

how to replace NULL values in a query

SQL Apprentice Question
I'm using MS SQL Server 2000. I have a simple table (Table1):

Table1:
ID Name PostCode
1 James 12345
2 Mandy 99100
3 John NULL
4 Alex NULL


how to build a query that list all Table1 data replace those with PostCode
NULL with Postcode '00000' ?


Result expected:
ID Name PostCode
1 James 12345
2 Mandy 99100
3 John 00000
4 Alex 00000

Celko Answers
Use ISNULL in an UPDATE, then go back and change the DEFAULT to
'00000'', if that is your convention. Remember, mop the floor but then
fix the leak.

No comments: