IF condition

Question

Post reply

IF condition

chinni-652949

Default port

Points: 1425
More actions
October 2, 2008 at 10:00 am

#382930

Need to check if the field has space and null
if (select column from table) not (null and ' ')
print 'value exists'
how to write IF condition for this??
I tried like
if (select column from table) in ('','null')
print 'nul'
else
print 'not null'
but it could not check null condition
it prints not null

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

Are you after a query or funciton

for a query

select COLUMN from TABLE where COLUMN is null

Let me know what you are trying to acheive and i can offer further help

RyanRandall SSChampion Points: 13623 More actions · Answer 2

if exists (select * from table where nullif(column, ' ') is null)

print 'column contains either a null or a space'

else

print 'column contains neither a null nor a space'

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

GSquared SSC Guru Points: 260824 More actions · Answer 3

If (select isnull(column, '') from table) != ''

print 'Not Null or Empty'

Else

print 'Null or Empty'

I'm using Empty for just a space.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

RyanRandall SSChampion Points: 13623 More actions · Answer 4

GSquared (10/2/2008)
If (select isnull(column, '') from table) != '' print 'Not Null or Empty' Else print 'Null or Empty'
I'm using Empty for just a space.

What if the table has no rows?

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

chinni-652949 Default port Points: 1425 More actions · Answer 5

declare @var varchar (20)

set @var = (SELECT distinct column from table where id = 1234 )

select @var

IF (@var is null) or (@var = '')

PRINT 'space'

else

print 'no space'

Dugi SSCoach Points: 17998 More actions · Answer 6

Interesting stuff here... :hehe:

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

GSquared SSC Guru Points: 260824 More actions · Answer 7

RyanRandall (10/2/2008)
GSquared (10/2/2008)
If (select isnull(column, '') from table) != '' print 'Not Null or Empty' Else print 'Null or Empty'
I'm using Empty for just a space.
What if the table has no rows?

I have to admit, I'm operating on the assumption that the inline query has more to it than the sample I put together.

Also, if there are no rows, it will print Null or Empty, which might be valid in this case, or might not, depending on the business-need for the query.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

It doesn't need to be that complicated if you understand that NULL is NOT nothing and cannot be compared to using relational operators...

IF (SELECT somecolumn FROM sometable) > ' '

PRINT 'Not NULL and Not BLANK'

ELSE

PRINT 'IS NULL or BLANK'

You'll also find that it runs just a wee bit faster if you have a very large data set...

Of course, none of the code in this format really solves the OP's original question unless we add a WHERE clause with the proper criteria... if the goal is to check the whole bloody table, something like the following might be more appropriate...

IF EXISTS (SELECT 1 FROM sometable WHERE somecolumn > ' ')

PRINT 'Not NULL and Not BLANK'

ELSE

PRINT 'IS NULL or BLANK'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)