November 15, 2002 at 6:39 am
Hi there, i'm having a small problem with
select distinct query. I guess the problem is that it works too good. I am trying to
get State names but i only want one of each.
The problem is that some records dont have anything in the state field (foreign customers). So i get an empty record at the very front. I dont seem to be able to get rid of it in any way i tried which is:
1:sticking a where clause at the end of select distinct: where state is not null and state<>''
2:trying to skip that record when i read my recordset with asp.
neither worked.
any suggestions??
November 15, 2002 at 6:52 am
select statename
from states
where state is not null
and state <> ""
group by statename
November 15, 2002 at 6:58 am
Thanks Klaas but that will not work.
First of because it returns duplicates and second it STILL returns that blank one.
November 15, 2002 at 7:00 am
select distinct statename
from states
where ISNULL(LTRIM(RTRIM(state)),'') != ''
-Dan
-Dan
November 15, 2002 at 7:12 am
Thanks Dan but i still get that blank record.
I dont know maybe there is some invisible character stuck in there. Is there any way to tell? I looks blank in enterprize manager.
it is really driving me crazy!
November 15, 2002 at 8:02 am
If it's a hidden char, then that is difficult to detect, maybe try this:
select distinct statename
from states
where state not like '%[^A-Z ]%' and state like '%[A-Z]%'
You can use this if you have case-sensitivity turned on: '%[^A-Za-z ]%'
-Dan
Edited by - dj_meier on 11/15/2002 08:02:59 AM
-Dan
November 15, 2002 at 11:14 am
Dan, that worked like a charm. Thanks a bunch!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply