How to eliminate NULL display space during SELECT

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

How to eliminate NULL display space during SELECT

Author

Topic

Arif
Starting Member

10 Posts

Posted - 2005-10-24 : 04:41:20

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

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-10-24 : 04:48:13

You cant do this with SELECT * but you could use SELECT ISNULL(ColumnName,'ValueToReplaceNull')
or put a default value on the column to eliminate nulls

Andy

Beauty is in the eyes of the beerholder

spirit1
Cybernetic Yak Master

11752 Posts

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

select name, coalesce(age, ''), coalesce(dept, '')
from test

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-24 : 04:51:24

Duplicate post
http://sqlteam.com/forums/topic.asp?TOPIC_ID=56811

Madhivanan

Failing to plan is Planning to fail

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-10-24 : 04:54:35

quote:
Originally posted by spirit1

select name, coalesce(age, ''), coalesce(dept, '')
from test

Go with the flow & have fun! Else fight the flow

That makes a change its usually me!
Beauty is in the eyes of the beerholder

Arif
Starting Member

10 Posts

Posted - 2005-10-24 : 05:48:58

Thanks Andy,Madhivanan,

I added one more fied dob as date type.

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

The same thing happens when coalsce(age,''),coalsce(dob,'') is used

how can make space if it is NULL.

Thanks in advance

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-10-24 : 06:09:52

you can't. that should be the presentation layer's job.
you can do
convert(varchar(20), isnull(dob, '')) as dob

data formatting should be done in front end!

Go with the flow & have fun! Else fight the flow

Arif
Starting Member

10 Posts

Posted - 2005-10-25 : 04:00:04

I used convert still date field displaying the date 1900-01-01 in formated way instead of NULL.

how can make space if it is NULL.

Regards

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-25 : 04:06:44

Ask your question at only Forum
I already answered to this question
http://sqlteam.com/forums/topic.asp?TOPIC_ID=56810

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-25 : 04:13:51

Select IsNull(Convert(Varchar(100),<Column_Name>),'')
From <Table_Name> Where <Column_Name> Is null

Complicated things can be done by simple thinking

Arif
Starting Member

10 Posts

Posted - 2005-10-26 : 01:36:17

Thanks Chirangkhabaria,

This the output I expected..

Thanks a lot

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-26 : 01:40:13

I already answered to this question
http://sqlteam.com/forums/topic.asp?TOPIC_ID=56811

It seems that you didnot look that

Madhivanan

Failing to plan is Planning to fail

Arif
Starting Member

10 Posts

Posted - 2005-10-26 : 02:08:00

Thanks Madhivanan,

I was little late late seeing your reply.

Thanks once again.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-26 : 02:11:16

>>I was little late late seeing your reply.

Thats why you need to post the same question at only one Forum

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources