if or case... - 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

if or case...

Author

Topic

pazzy11
Posting Yak Master

145 Posts

Posted - 2007-05-11 : 06:38:26

Hi
I wanna select a load of data ..

but in case of a null i wanna replaced it with certain data ..

so its something like
SELECT
x if null then '',
y if null then '',
z if null then '',
FROM table_name

what is the exact syntax ?
cheers..

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-11 : 06:44:18

Select case when x is null then '' else x end ,
case when y is null then '' else x end ,
case when z is null then '' else x end ,
....
.....
from table name

--Ahamed

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-11 : 07:06:39

neither -- use isnull() or coalesce().

- Jeff
http://weblogs.sqlteam.com/JeffS

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-11 : 07:11:15

or
ISNULL(col,'')
COALESCE(col,'')

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-11 : 09:57:18

quote:
Originally posted by jsmith8858

neither -- use isnull() or coalesce().

- Jeff
http://weblogs.sqlteam.com/JeffS

My internet connection was slow at that time and I didnt see your reply when I posted

Madhivanan

Failing to plan is Planning to fail

pazzy11
Posting Yak Master

145 Posts

Posted - 2007-05-11 : 11:58:13

ehh ..
so i have :
SELECT
CASE when x is null then '' else x,
CASE when y is null then '' else y,
.
.
etc...

pazzy11
Posting Yak Master

145 Posts

Posted - 2007-05-11 : 12:02:58

ok that didn't work...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-05-11 : 12:05:11

quote:
Originally posted by pazzy11

ehh ..
so i have :
SELECT
CASE when x is null then '' else x,
CASE when y is null then '' else y,
.
.
etc...

It is better to do as others have suggested and to COALESE:

SELECT 
	COALESCE(x, ''),
	COALESCE(y, ''),
	COALESCE(z, '')
FROM 
	table_name

-Ryan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-11 : 23:27:16

quote:
Originally posted by pazzy11

ok that didn't work...

How do we know why it didnt work until you give more informations?

Madhivanan

Failing to plan is Planning to fail

pazzy11
Posting Yak Master

145 Posts

Posted - 2007-05-14 : 03:52:30

ok so COALESCE concatenates the output ?
so if i add a '' to all output none will be NULL ??

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-14 : 03:56:23

"ok so COALESCE concatenates the output ?"

No.
Why don't you read what COALESCE() does in SQL Server help?

And what exactly you want? to concatenate all column values?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pazzy11
Posting Yak Master

145 Posts

Posted - 2007-05-14 : 08:52:34

ok .. read .. for my situation it may as well do that, it insures there will be no null output..

Im getting this error though
Arithmetic overflow error converting expression to data type datetime.

Now i know what it means, but problem is all vars match up on source & destination,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-14 : 08:54:52

Read about date limits for DATETIME and SMALLDATETIME in Books Online...

Peter Larsson
Helsingborg, Sweden

pazzy11
Posting Yak Master

145 Posts

Posted - 2007-05-14 : 09:42:37

hmm.. 1 database has Date field for a corresponding field that is datetime on the other database ? would this cause that error ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-14 : 09:46:03

Date field? Doesn't seem like SQL Server database.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"