Checking for Nulls while selecting

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

SQL Server 2000 Forums

Transact-SQL (2000)

Checking for Nulls while selecting

Author

Topic

vidhyasagar
Starting Member

1 Post

Posted - 2005-07-29 : 03:55:47

Hi!
I need to select some 73 columns while checking if they have null values in them.

i.e Select ISNULL(Column-name, '').....FROM Table_name

Instead of writing the ISNULL() part 73 times, is there is any waycan i do it in one shot.

Kristen
Test

22859 Posts

Posted - 2005-07-29 : 04:19:02

This will give you a list of all the columns which you could cut&paste from


SELECT	'ISNULL(' + COLUMN_NAME + ', ''''), '
FROM	INFORMATION_SCHEMA.COLUMNS
WHERE	    TABLE_SCHEMA = 'dbo'
	AND TABLE_NAME = 'MyTable'
ORDER BY ORDINAL_POSITION

Kristen

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-07-29 : 04:21:14

NO, you have to address each column in any case - you may as well type it all out.

You can use coalesce to return the first non-null value form a list of columns - just like isnull, only thing is - this will check all columns specified and return it into the column in which it appears in the select statement.

select coalesce(col1, col2, col3, col4, col5, '')

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources