NULL in result - 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

SQL Server 2000 Forums

Transact-SQL (2000)

NULL in result

Author

Topic

Job
Yak Posting Veteran

69 Posts

Posted - 2005-08-18 : 18:05:48

Many times we will take the result of a query and save to Excel or some other program. Then we have to go and replace all of the 'NULLS'. Is there a way to run a query so that the NULLS show up as blanks?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-18 : 18:12:43

Yes, you can use the COALESCE function.

SELECT COALESCE(Column1, '')
FROM Table1

Tara

Job
Yak Posting Veteran

69 Posts

Posted - 2005-08-18 : 18:32:38

Perfect. Thanks Tara. If I have a lot of columns that will have the null values, is there a way to set that for the entire query so I don't have to put the COALESCE in front of every field?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-18 : 18:58:52

Yes but the code would be inefficient.

Tara

Kristen
Test

22859 Posts

Posted - 2005-08-19 : 03:36:33

"so I don't have to put the COALESCE in front of every field"

Generate it instead?


SELECT	CASE WHEN ORDINAL_POSITION = 1 THEN '  ' ELSE ', ' END
	+ 'COALESCE(' + COLUMN_NAME + ', '''')'
FROM	INFORMATION_SCHEMA.COLUMNS
WHERE	TABLE_SCHEMA = 'dbo'
	AND TABLE_NAME = 'MyTable'
ORDER BY ORDINAL_POSITION

Kristen

jaffa_cree
Starting Member

13 Posts

Posted - 2005-08-19 : 03:50:38

quote:
Originally posted by Job

Many times we will take the result of a query and save to Excel or some other program. Then we have to go and replace all of the 'NULLS'. Is there a way to run a query so that the NULLS show up as blanks?

If you are using QA, you can set the output to 'results to file' and save the results as a CSV. Then you can resave the CSV as an XLS.

Alternatively, you can also use the ISNULL function.
SELECT ISNULL(dbo.column1, '') FROM table1

If the column datatype is a non char, like a datetime or int, you would need to convert the column to a varchar/char first.

Kristen
Test

22859 Posts

Posted - 2005-08-19 : 04:48:24

"SELECT ISNULL(dbo.column1, '') FROM table1"

Sorry to be picky, but I think you meant

table1.column1
instead of
dbo.column1

and "ISNULL" is not ANSI, thus COALESCE is preferable (both are useless words IMHO - IsNull implies a test for NULLness, not a substitution for a NULL value, and COALESCE is an obscure word - to say the least!)

Kristen

jaffa_cree
Starting Member

13 Posts

Posted - 2005-08-19 : 05:03:39

quote:
Originally posted by Kristen

"SELECT ISNULL(dbo.column1, '') FROM table1"

Sorry to be picky, but I think you meant

table1.column1
instead of
dbo.column1

and "ISNULL" is not ANSI, thus COALESCE is preferable (both are useless words IMHO - IsNull implies a test for NULLness, not a substitution for a NULL value, and COALESCE is an obscure word - to say the least!)

Kristen

Whoops, my bad.
Thanks for pointing that out Kristen.

Job
Yak Posting Veteran

69 Posts

Posted - 2005-08-19 : 09:22:13

Thank you everyone for your input. I'm going to test the different suggestions to see which one is most efficient for me.

Cheers,

Job

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources