how to replace NULL values in a query?

[pedestrian via SQLMonster.com]

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

[Anith Sen]

Lookup ISNULL & COALESCE functions in SQL Server Books Online

[MeanOldDBA]

DECLARE @table1 TABLE ([ID] INT, [Name] VARCHAR(55), PostCode CHAR(5))

INSERT @table1([ID], [Name], PostCode)
SELECT 1,'James', 12345 UNION ALL
SELECT 2,'Mandy', 99100 UNION ALL
SELECT 3,'John', NULL UNION ALL
SELECT 4,'Alex', NULL

SELECT [ID], [Name], ISNULL(PostCode,'00000')
FROM @table1

SELECT [ID], [Name], COALESCE(PostCode,'00000')
FROM @table1
--
MeanOldDBA
***@hotmail.com
http://weblogs.sqlteam.com/derrickl

When life gives you a lemon, fire the DBA.

Post by pedestrian via SQLMonster.com
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' ?
ID Name PostCode
1 James 12345
2 Mandy 99100
3 John 00000
4 Alex 00000
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200605/1

[pedestrian via SQLMonster.com]

Thanks for replying....

I have solved it using :
Select Case When PostCode IS NULL THEN '00000' ELSE PostCode End

[--CELKO--]

Use IFNULL 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.

[Tony Rogerson]

There is no IFNULL operator/function nor statement in MICROSOFT SQL SERVER.

Please check books online that your standard SQL is implemented in the
product.

You mean ISNULL.
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

Post by --CELKO--
Use IFNULL 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.

[Jamie Collins]

Post by Tony Rogerson

Post by --CELKO--
Use IFNULL in an UPDATE, then go back and change the DEFAULT to
'00000'', if that is your convention.

There is no IFNULL operator/function nor statement in MICROSOFT SQL SERVER.
Please check books online that your standard SQL is implemented in the
product.
You mean ISNULL.

I think in this case it was a typo (or Spoonerism <g>) and he meant
NULLIF e.g.

SELECT COALESCE(NULLIF(first_name, '') + ' ', '') + last_name FROM
Employees

but of course formatting should not be done in the database <g>.

Jamie.

--

[Tony Rogerson]

Thanks Jamie, its a spoonerism --celko-- uses in all his posts with IFNULL I
just presumed it was part of SQL 99.

Post by Jamie Collins
but of course formatting should not be done in the database <g>.

;) - check out my blog post on this...
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/05/11/429.aspx for
my view on this....

tony.
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

Post by Jamie Collins

Post by Tony Rogerson

Post by --CELKO--
Use IFNULL in an UPDATE, then go back and change the DEFAULT to
'00000'', if that is your convention.

There is no IFNULL operator/function nor statement in MICROSOFT SQL SERVER.
Please check books online that your standard SQL is implemented in the
product.
You mean ISNULL.

I think in this case it was a typo (or Spoonerism <g>) and he meant
NULLIF e.g.
SELECT COALESCE(NULLIF(first_name, '') + ' ', '') + last_name FROM
Employees
but of course formatting should not be done in the database <g>.
Jamie.
--