Want to select Records with null values

Question

Want to select Records with null values

sar_kan25

SSC-Addicted

Points: 460
More actions
December 18, 2008 at 5:04 am

#125972

I have a table Info with Name, address,Mob.No.,Designation as Columns.
Some of the records in the table contain null values.
Now i want to write the select query which will give me all the records with atleast one of the field contain null value
How to do this?
can anybody help me?

Viewing 5 posts - 1 through 4 (of 4 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

sar_kan25 (12/18/2008)
I have a table Info with Name, address,Mob.No.,Designation as Columns.
Some of the records in the table contain null values.
Now i want to write the select query which will give me all the records with atleast one of the field contain null value
How to do this?
can anybody help me?

SELECT [Name], address, [Mob.No.], Designation

FROM mytable

WHERE [Name] IS NULL OR address IS NULL OR [Mob.No.] IS NULL OR Designation IS NULL

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

GSQL SSC Enthusiast Points: 184 More actions · Answer 2

If these columns are all character datatypes you could use:

select *

from table

where Name+address+Mob.No.+Designation is null

Otherwise, assuming Mob.No. is a numeric datatype:

select *

from table

where Name+address+convert(varchar(10), Mob.No.)+Designation is null

Vladan SSC-Insane Points: 22072 More actions · Answer 3

George Giddens (12/21/2008)
If these columns are all character datatypes you could use:
select *
from table
where Name+address+Mob.No.+Designation is null

I would not recommend this solution, since it depends on settings that can be different in different situations. The first posted solution is more universal. Try this for example:

SET CONCAT_NULL_YIELDS_NULL ON

SELECT 'A' + NULL + 'B'

SET CONCAT_NULL_YIELDS_NULL OFF

SELECT 'A' + NULL + 'B'

GSQL SSC Enthusiast Points: 184 More actions · Answer 4

GSQL

SSC Enthusiast

Points: 184

December 23, 2008 at 2:41 am

#916629

Point taken. Thanks