SQL command operator meaning field is empty 3

[bazil2]

(Elementary user)

Can anyone tell me the correct operator (e.g. = <> !) for checking fields that have no assigned value.

In layman's terms, I'm looking to build a query like this:

"Find me all the records that have an empty surname" AND "Find me all the records that have an empty age" hence:

Surname=""; AND Age="";

Best regards

 

[r937]

you're looking for IS NULL



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[bazil2]

Thanks very much!

If I wanted to do the opposite, i.e. look for files that DO have an assigned value, would the operator be:

IS NOT NULL

 

[simian336]

Would the operator be: IS NOT NULL? Yes

However, keep in mind that is null is different then '' or ' '.

To get all three you could use

where len(rtrim(yourfield))=0

Simi

 

[gmmastros]

simian336,

It's even easier than that. The len function already rtrims the data before determining the length. Check it out.

Select len(' ')
Select len('')
Select len('A        ')

To get all rows where there is something in the data (not null or empty string), I usually use this...

Select Columns
From   Table
Where  ColumnName > ''

When NULL is compared to empty string, it is excluded and so is all of the rows with empty strings or just spaces. Ex:

Declare @Test Table(Data VarChar(20))

Insert Into @Test Values(NULL)
Insert Into @Test Values('')
Insert Into @Test Values(' ')
Insert Into @Test Values('   ')
Insert Into @Test Values('Real data here')

Select *
From   @Test
Where  Data > ''

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

I think the user was asking to find all Empty OR Null values for SurName and Age.

So to use your example George, I think this is what he is trying to get.

Declare @Test Table
(
		FN VarChar(10),
		SN VarChar(12),
		AGE int
)

Insert Into @Test Values('John','Smith',21)
Insert Into @Test Values('Jane','Smith',23)
Insert Into @Test Values('Sean','MacHenry',21)
Insert Into @Test Values('Jake','Lake',43)
Insert Into @Test Values('Jane','Doe',63)
Insert Into @Test Values('John','Doe',55)
Insert Into @Test Values('Tricky','Licky',89)
Insert Into @Test Values('Fred','Ott',35)
Insert Into @Test Values('One','Inthebush',25)
Insert Into @Test Values('Two','Inthehand',26)
Insert Into @Test Values('Lacky','',22)
Insert Into @Test Values('Lucky','  ',66)
Insert Into @Test Values('Heidi',Null,21)
Insert Into @Test Values('Janice','Quinlyn',Null)
Insert Into @Test Values('Franky','Thomas',0)

Select *
From   @Test
Where	(Len(SN) = 0 OR SN is Null)
OR		(AGE < 1 OR AGE Is Null)

Result:
FN         SN           AGE
---------- ------------ -----------
Lacky                   22
Lucky                   66
Heidi      NULL         21
Janice     Quinlyn      NULL
Franky     Thomas       0


Select * From @Test
Result:
FN         SN           AGE
---------- ------------ -----------
John       Smith        21
Jane       Smith        23
Sean       MacHenry     21
Jake       Lake         43
Jane       Doe          63
John       Doe          55
Tricky     Licky        89
Fred       Ott          35
One        Inthebush    25
Two        Inthehand    26
Lacky                   22
Lucky                   66
Heidi      NULL         21
Janice     Quinlyn      NULL
Franky     Thomas       0

Thanks

John Fuhrman