Null in the result set - 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 in the result set

Author

Topic

joy_hw
Starting Member

8 Posts

Posted - 2010-01-27 : 22:42:09

I'm using the microsoft SQL server management studio(SQL 2008). I'm getting "NULL" in the result set and I think I know why I'm getting this, but I want to display "NULL" as just blank.
If I run the same codes in SQL query analyzer and set the result in text, I get the blank, but this doesn't seem to work in SQL management studio.
Could you please let me know if there is a way to do it?
Thank you.

divyaram
Posting Yak Master

180 Posts

Posted - 2010-01-27 : 23:24:20

select ISNULL(columnname+'') from tablename

Regards,
Divya

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-28 : 00:16:07

quote:
Originally posted by divyaram

select ISNULL(columnname+'') from tablename

Regards,
Divya

It should be

select ISNULL(columnname,'') from tablename

Madhivanan

Failing to plan is Planning to fail

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-01-28 : 00:16:21

Correct Syntax

select ISNULL(columnname,'') from tablename

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 03:32:36

If you are not already using ISNULL() you would be better to adopt the more awkwardly named COALESCE(columnname,'') as it avoids some datatype implicit conversion issues that ISNULL() suffers from, and allows more than 2 parameters

Either will be identical for this particular job ... I'm thinking of your long term usage

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-28 : 05:59:47

quote:
Originally posted by Kristen

If you are not already using ISNULL() you would be better to adopt the more awkwardly named COALESCE(columnname,'') as it avoids some datatype implicit conversion issues that ISNULL() suffers from, and allows more than 2 parameters

Either will be identical for this particular job ... I'm thinking of your long term usage

you mean this?

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/10/04/isnull-or-coalesce.aspx

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 06:50:22

That's the puppy! - thanks Visakh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-28 : 06:51:37

quote:
Originally posted by Kristen

That's the puppy! - thanks Visakh

No problem

joy_hw
Starting Member

8 Posts

Posted - 2010-01-28 : 09:49:21

ISNULL() works. This is great.

One of the columns is numeric, so neither ISNULL nor COALESCE worked.
However, the link that Visakh posted gave me an idea of using ISNUMERIC. I just used "Case" statement to return any "NULL" in this numeric field to 0. I was getting the converting varchar error with just using ISNULL or COALESCE.

This is really helpful.

Thank you so much guys.

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 09:54:15

Yeah, well you can't do COALESCE(SomeNumericColumn, '') because that is mixing types.

You could do COALESCE(CONVERT(varchar(20), SomeNumericColumn), '') though ... because then both parameters are then Strings

If you want to convert NULL to ZERO (i.e. maintaining datatype) you can do

COALESCE(SomeNumbericColumn, 0)

but note that you won't then be able to tell the difference between an actual Zero value, in the column, and a Null value - so you are losing some data quality and/or possibly confusing the recipient of the data

joy_hw
Starting Member

8 Posts

Posted - 2010-01-28 : 11:18:32

You are right about the data quality. I was just focusing on removing "NULL" from the result set. I really wanted blank instead of 0 for the null value.

I just used COALESCE(CONVERT(varchar(2),columnname),''), and it worked without errors.

For me, it's still challenging to deal with datatype error.

Thank you so much again for your help.

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 11:43:52

Excellent! Glad you've got that going.

SQL's error messages could be a LOT more helpful. 20 years experience is a substitute for poor error messages, but not as easily acquired as an error message with cruise-missile quality pinpointing

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-30 : 00:37:26

quote:
Originally posted by joy_hw

ISNULL() works. This is great.

One of the columns is numeric, so neither ISNULL nor COALESCE worked.
However, the link that Visakh posted gave me an idea of using ISNUMERIC. I just used "Case" statement to return any "NULL" in this numeric field to 0. I was getting the converting varchar error with just using ISNULL or COALESCE.

This is really helpful.

Thank you so much guys.

ISNUMERIC should also be used with care. see below

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/enhanced-isnumeric-function.aspx

joy_hw
Starting Member

8 Posts

Posted - 2010-02-02 : 23:51:00

Thank you for the useful information, visakh16.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 03:26:46

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources