check for null, empty or 0 - 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 2005 Forums

Transact-SQL (2005)

check for null, empty or 0

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2007-11-01 : 08:39:55

Hi,

I have made a join between 2 tables TableA and TableB

I want to check for the following :

Insert into TableA
select isnull(TableA.fname,TableB.fname) || isempty(TableA.fname,TableB.fname), isnull(TableA.lname,TableB.lname)||isempty(TableA.fname,TableB.fname), isnull(TableA.age,TableB.age)||iszero(TableA.fname,TableB.fname)
from TableA right join TableB
On TableA.id = TableB.id

Now there isnt a function called isempty or iszero. How to implement this query properly. I want to basically check null and then empty or zero, for columns fname and lname, age

thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-01 : 08:42:40

Replace

isempty(TableA.fname,TableB.fname),

with

case when TableA.fname='' then TableB.fname else TableA.fname end

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-01 : 08:43:36

Wait. Are you using SQL Server?

Madhivanan

Failing to plan is Planning to fail

lols
Posting Yak Master

174 Posts

Posted - 2007-11-01 : 09:52:57

thanks madhivanan,

yes, i use sql server 2005.

Can I do something like :

select isnull(TableA.fname,TableB.fname) || case when TableA.fname='' then TableB.fname else TableA.fname end

that is checking either of these 2 conditions

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-01 : 09:55:13

what do you mean by ||?
concatenation?

Madhivanan

Failing to plan is Planning to fail

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-01 : 09:55:57

isn't that oracle?

Em

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-01 : 10:07:25

quote:
Originally posted by elancaster

isn't that oracle?

Em

Thats why I asked OP

Madhivanan

Failing to plan is Planning to fail

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2007-11-01 : 11:18:23

I think the statement below will work in SQL Server, assuming when you say "iszero" you are talking about the length:

select case when TableA.fname='' then TableB.fname when Len(TableA.fname) = 0 then TableB.fname else isnull(TableA.fname,TableB.fname) end

Jeremy W. Oldham

Kristen
Test

22859 Posts

Posted - 2007-11-01 : 13:00:00

I'll use COALESCE instead of ISNULL for my example. You can use either, but there are "issues" with ISNULL.

COALESCE(TableA.fname, TableB.fname)

this will get you TableA.fname if it is not null, otherwise TableB.fname (whether it is NULL of not)

COALESCE(NullIf(TableA.fname, ''), NullIf(TableB.fname, ''))

This will convert TableA.fname and TableB.fname to NULL if they are empty. Then as above.

COALESCE(NullIf(NullIf(TableA.fname, ''), '0'), NullIf(NullIf(TableB.fname, ''), '0'))

Ditto, but also converts the string "0" to NULL.

HOWEVER, this is only going to work if TableA.fname is a string datatype. And the "zero" match will only work if it contains exactly "0". If it contains " 0" that won't match.

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2007-11-01 : 13:03:24

quote:
Originally posted by elancaster

isn't that oracle?

Em

Or DB2

You could create a function to do all of that

But what result do you want if it's TRUE?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2007-11-01 : 14:06:44

Thanks for the info Kristen. I have been writing CASE statements for a while. Looks like I need to become familiar with the COALESCE and NullIf functions. ;)

Jeremy W. Oldham

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources