CODEWITHSUNDEEP
sqlvarcharisnullorempty
user3199317
user3199317

Reputation: 29

Returning varchars that are not null or empty SQL

I have a column in SQL that is varchar. I need it to return anything with a value.

Example...

select * from students where StudentID <> ''

Is this the correct way of doing it? I've tried is not null but then it returns anything that is empty as well.

Thanks

Upvotes: 1

Views: 489

Answers (6)

Mitz
Mitz

Reputation: 561

Try using ISNULL()

select * from student where isnull(studentid,'') <> ''

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460108

You have to handle the case that it's not null separately because you cannot compare with null-values. They are neither equal nor unequal to any other value(incl. null). NULL means "unknown value", so any comparison with any actual value makes no sense

....
WHERE StudentID IS NOT NULL AND  StudentID  <> ''

You could use ISNULL(StudentID,'') <> '' (or COALESCE). But i think this is more efficient.

Upvotes: 0

TGH
TGH

Reputation: 39248

select * from students where StudentID <> '' AND StudentID IS NOT NULL

You can target both white space and null.

Upvotes: 1

crthompson
crthompson

Reputation: 15865

I would suggest using coalesce:

select * from students where coalesce(StudentID, '') <> ''

This will turn nulls into empty strings and disallow them. This has the added bonus of restricting empty strings as well.

A null is not equal to anything, not even another null, so a simple <> doesnt work.

Upvotes: 2

Paul Gibson
Paul Gibson

Reputation: 634

try this:

select * from students where StudentID is not null

Upvotes: 0

anousss
anousss

Reputation: 347

There's something call NOT NULL

SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NOT NULL

Is that helping ?

Upvotes: 0

Related Questions