How to eliminate NULL with space

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

Old Forums

CLOSED - General SQL Server

How to eliminate NULL with space

Author

Topic

Arif
Starting Member

10 Posts

Posted - 2005-10-24 : 04:43:12

I have a table with 3 fields. when I type
select * from test -- I am getting the results as below.

NAME AGE DEPT

AAA 23 AOD
BBB 27 NULL
CCC NULL NULL
DDD 23 POD

DEPT,AGE are displayed with "NULL" WHEN THERE IS NO value for that field . How can I eliminate this. I need space instead of NULL. When I export to text file there also contains NULL. Let me know how can I eliminate this.

Thanks in advance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-24 : 04:49:26

Select NAME, AGE, ISNULL(DEPT,'') from test

Madhivanan

Failing to plan is Planning to fail

Arif
Starting Member

10 Posts

Posted - 2005-10-24 : 05:40:29

Thanks Madhivanan,

I added one more fied dob as date type.

when I used select name,isnull(dob,'') from test shows date
'1900-01-01' instead of space in dob field.

how can make space if it is NULL.

Thanks in advance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-24 : 05:47:10

Isntead of

isnull(dob,'')

use this

isnull(convert(varchar,dob,101),'')

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-10-24 : 14:59:04

Arif: Why are you wanting to do this?

If its to display to "End Users" it would be better to get your presentation layer to do this, rather than SQL Server. You are obscuring the data type, and also the distinction between "NULL" and "Empty String" which are very different.

My "presentation layer" displays NULL as a blank string. But it is sure to display a numeric column "right hand justified". Whereas if I converted a numeric column to a string, to suppress the display of Nulls, then that column would be treated as a string, not a numeric, and left hand justified.

Kristen

Arif
Starting Member

10 Posts

Posted - 2005-10-26 : 02:04:39

Thanks Kristen for your advise. I am not converting numeric to character. This only for display purpose. I send the query out put to a thirdparty application. Which displays the output of the query.

Earlier the screen shows 'NULL' where ever there is a null value. Which looks very odd. So I decided to replace it with space.

Now I user the following query.

select isnull(convert(varchar(100),<columnname>),'') from <tablename>

Regards,
Arif

Kristen
Test

22859 Posts

Posted - 2005-10-26 : 02:17:45

"thirdparty application"

... needs some major surgery I reckon!

"select isnull(convert(varchar(100),<columnname>),'') from <tablename>"

If I had to do this for a non-string datatype I would be very unhappy.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources