| Author |
 Topic  |
|
|
esthera
Flowing Fount of Yak Knowledge
1355 Posts |
 Posted - 10/16/2004 : 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
Israel
86 Posts |
Posted - 10/16/2004 : 17:17:01
|
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
or
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
|
Edited by - VIG on 10/16/2004 17:37:42 |
 |
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 10/17/2004 : 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
United Kingdom
22191 Posts |
Posted - 10/17/2004 : 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
Israel
86 Posts |
Posted - 10/17/2004 : 14:08:20
|
You have convinced me  |
Edited by - VIG on 10/17/2004 14:08:54 |
|
|
|
jonasalbert20
Constraint Violating Yak Guru
Philippines
300 Posts |
Posted - 10/17/2004 : 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
Slovenia
11741 Posts |
Posted - 10/18/2004 : 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
India
277 Posts |
Posted - 10/18/2004 : 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
USA
7423 Posts |
Posted - 10/18/2004 : 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
Slovenia
11741 Posts |
Posted - 10/18/2004 : 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
Philippines
300 Posts |
Posted - 10/18/2004 : 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... |
|
|
| |
Topic |
|
select only fields not blank
