SQL statement question

[Jason Reljac]

Howdy,

I have a table full of names, last & first, and I would like to do a SQL
select to get all of the last names that start with A-H
(I'll run another for I-P & Q-Z). The resulting last name, first name combo
needs to end up in a drop down box.

I can get the results of a query into a drop down just fine, but I can't get
the SQL statement to work right.
I've tried a few statements including LIKE, but nothing seems to work. Any
ideas on how I could go about doing this?

Thanks,
Jason

[JerryH]

Re: SQL statement question

You didn't state clearly whether the firstnames and lastnames were in the
same field together or if they were different fields. If they are in
different fields, then Leslie's suggesting will work great. If your
firstnames and lastnames are in the same field, then you need something more
like this:

Leslie's Suggestion:
SELECT * FROM table WHERE lname REGEXP "^[A-H]"

Modifed for fullname field:
SELECT * FROM table WHERE fulllname REGEXP " [A-H]"

Notice that Leslie used a '^' symbol for start of line, where I used a ' '
(space). The space idea will only work if you can assume there is exactly
one space in each field, which is a risky assumption. If the first names
and last names are combined and the number of spaces in the field are
unpredicatable, that is a real challenge. :-)

Jerry

"Jason Reljac" <jmr109@hotmail .com> wrote in message
news:vhisl830s4 v002@corp.super news.com...[color=blue]
> Howdy,
>
> I have a table full of names, last & first, and I would like to do a SQL
> select to get all of the last names that start with A-H
> (I'll run another for I-P & Q-Z). The resulting last name, first name[/color]
combo[color=blue]
> needs to end up in a drop down box.
>
> I can get the results of a query into a drop down just fine, but I can't[/color]
get[color=blue]
> the SQL statement to work right.
> I've tried a few statements including LIKE, but nothing seems to work.[/color]
Any[color=blue]
> ideas on how I could go about doing this?
>
> Thanks,
> Jason
>
>[/color]

[Agelmar]

Re: SQL statement question

JerryH wrote:[color=blue]
> You didn't state clearly whether the firstnames and lastnames were in
> the same field together or if they were different fields. If they
> are in different fields, then Leslie's suggesting will work great.
> If your firstnames and lastnames are in the same field, then you need
> something more like this:
>
> Leslie's Suggestion:
> SELECT * FROM table WHERE lname REGEXP "^[A-H]"
>
> Modifed for fullname field:
> SELECT * FROM table WHERE fulllname REGEXP " [A-H]"
>
> Notice that Leslie used a '^' symbol for start of line, where I used
> a ' ' (space). The space idea will only work if you can assume there
> is exactly one space in each field, which is a risky assumption. If
> the first names and last names are combined and the number of spaces
> in the field are unpredicatable, that is a real challenge. :-)
>
> Jerry[/color]

If they are in separate fields (there is a lname field or something like
that) then both of you are nuts for using REGEXPs for such a simple
thing...just do WHERE lname BETWEEN 'aaa' AND 'hzz' etc. (Or if you are
worried about names starting with hzz, for example, hzzsomething, you can do
WHERE lname >= 'A' AND lname < 'I' (note that that's an I in quotes, not a
T...)

Using regexps is way overkill and will likely be slower.