null help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

null help

Author

Topic

black_pearl
Starting Member

15 Posts

Posted - 2006-05-17 : 22:39:05

hello,

how can i replace null values in a certain column with a string?

thanks

__black_pearl__

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2006-05-17 : 23:19:17

Were you the one asking this on the Microsoft newsgroups?


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
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

black_pearl
Starting Member

15 Posts

Posted - 2006-05-18 : 01:16:38

does this query permanently replace the null values? because that's what i need.
thanks.

__black_pearl__

Kristen
Test

22859 Posts

Posted - 2006-05-18 : 01:21:47

Derrick was demonstrating how you could replace them as part of your SELECT routines.

You could do an UPDATE to gt rid of them - but presumably new rows could then occur with NULL values. So you should also set the column to NOT NULL to prevent new ones occurring.

Using UPDATE top remove them:


UPDATE U
SET    MyColumn = ... some suitable value ...
FROM   MyTable AS U
WHERE  MyColumn IS NULL

Take a backup first please!!!

Kristen

black_pearl
Starting Member

15 Posts

Posted - 2006-05-18 : 01:26:24

thanks! you rock!

__black_pearl__

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-18 : 01:59:59

Also, If you use Front End application you can handle NULL there. If you dont want to have that column NULL then set a Default value

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources