select only fields not blank - 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 2000 Forums

Transact-SQL (2000)

select only fields not blank

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-10-16 : 15:39:23

I now have a select
"select firstname,lastname,phonenumber,cellphonenumber,fax from users"

Is there anyway to change it but selecting only those fields(from the above list) that are not blank?

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-10-16 : 17:17:01

[code]select firstname,lastname,phonenumber,cellphonenumber,fax
from users
where nullif(firstname,'') is not null and
nullif(lastname,'') is not null and
nullif(phonenumber,'') is not null and
nullif(cellphonenumber,'') is not null and
nullif(fax,'') is not null[/code]
or
[code]select firstname,lastname,phonenumber,cellphonenumber,fax
from users
where len(firstname) > 0 and
len(lastname) > 0 and
len(phonenumber) > 0 and
len(cellphonenumber) > 0 and
len(fax) > 0 [/code]

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-17 : 10:13:56

nullif(firstname,'') is not null
odd why not
firstname <> ''
or
coalesce(firstname,'') <> ''
if you want to allow for nulls.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2004-10-17 : 10:34:01

I quite like
NullIf(MyColumn, SomeValue) IS NULL
as a test where a column might be a specific value, or NULL.

Dunno why I prefer it to COALESCE() though ...

Feels like a LOGIC test, rather than a STRING EXPRESSION I suppose ...

Kristen

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-10-17 : 14:08:20

You have convinced me

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2004-10-17 : 21:04:38

To check if it is null? we can use the isnull function... Now if it is null we can replace it with no value then by using the len function we can determine if the field is blank!

I hope this querry will help...

SELECT firstname,lastname,phonenumber,cellphonenumber,fax FROM users
WHERE len(isnull(firstname,'')) <> 0 and
len(isnull(lastname,'')) <> 0 and
len(isnull(phonenumber,'')) <> 0 and
len(isnull(cellphonenumber,'')) <> 0 and
len(isnull(fax,'')) <> 0

Want Philippines to become 1st World COuntry? Go for World War 3...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-18 : 04:02:19

jonas:
that's true, but using two functions is always slower than using one

Go with the flow & have fun! Else fight the flow

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-18 : 07:12:50

Esther,
Do you want to select rows with "not null" columns or do you want to omit the columns in the output that has all nulls (ex: if none of the customers have fax numbers, omit the FAX column in the output) ?

If it's the former, follow the gurus above.
If it's the later, dump the output into a temp table and use "ALTER TABLE" to drop the columns without Non-Null values.

Hemanth Gorijala
BI Architect / DBA...(yuhoo!!! and now, an "Yak Master")

Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-18 : 09:06:05

i have a feeling that what he/she is really after has nothing to do with what we are guessing .....

- Jeff

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-18 : 09:55:28

could it be that he wants this?


-- set ESP ON

create procedure spMySproc
(
@firstname varchar(50),
@lastname varchar(50),
@phonenumber varchar(50),
@cellphonenumber varchar(50),
@fax varchar(50)
)
as

select 	firstname, lastname, phonenumber, cellphonenumber, fax 
from 	users
where 	(firstname = @firstname or @firstname is null) and 
	(lastname = @lastname or @lastname is null) and 
	(phonenumber = @phonenumber or @phonenumber is null) and 
	(cellphonenumber = @cellphonenumber or @cellphonenumber is null) and 
	(fax  = @fax or @fax is null)

go

exec spMySproc 'joe', 'young', null, null, '123456789'

-- set ESP OFF

Go with the flow & have fun! Else fight the flow

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2004-10-18 : 20:46:02

quote:
Originally posted by spirit1

jonas:
that's true, but using two functions is always slower than using one

Go with the flow & have fun! Else fight the flow

We can put it on stored proc to fasten the querry.

Want Philippines to become 1st World COuntry? Go for World War 3...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources