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]

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" <jm****@hotmail.com> wrote in message
news:vh************@corp.supernews.com...

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

[Agelmar]

JerryH wrote:

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

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.