Обсуждение: SQL Where LIKE - Range it!

H-

I've found the docs on how to select a list of rows from a table were
all the records have a last name starting with 'W%'.
select * from table where last_name LIKE 'W%'

What I'd like to do is pull a list of records where there is a range
of last names; say from A - F.
select * from table where last_name LIKE 'A%' AND last_name LIKE 'F%'
- for example.

The above code I've tried for this doesn't seem to work as I'd expect
it too?
I've even done
select * from table where last_name LIKE 'A%' AND LIKE 'F%'

Can anyone provide some details or insights on how to accomplish this?


Thanks. Much appreciated.
-Steagus

Oops.

That should have been: last_name >='A' and last_name<'G'  (not <=)

Calvin
--
Calvin Dodge
Certified Linux Bigot (tm)
http://www.caldodge.fpcc.net

    select * from table where last_name ~ '^[A-F]';
or
    select * from table where last_name between 'A' and 'G';
or
    select * from table where last_name >='A' and last_name<'G'

The second one is broken if last_name='G' returns something.
Use ~* in first example to ignore case.

Frank

At 08:16 PM 4/26/01 GMT, you wrote:
>H-
>
>I've found the docs on how to select a list of rows from a table were
>all the records have a last name starting with 'W%'.
>select * from table where last_name LIKE 'W%'
>
>What I'd like to do is pull a list of records where there is a range
>of last names; say from A - F.
>select * from table where last_name LIKE 'A%' AND last_name LIKE 'F%'
>- for example.
>
>The above code I've tried for this doesn't seem to work as I'd expect
>it too?
>I've even done
>select * from table where last_name LIKE 'A%' AND LIKE 'F%'
>
>Can anyone provide some details or insights on how to accomplish this?
>
>
>Thanks. Much appreciated.
>-Steagus
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://www.postgresql.org/search.mpl
>

On Thu, 26 Apr 2001, Steagus wrote:

>
> What I'd like to do is pull a list of records where there is a range
> of last names; say from A - F.
> select * from table where last_name LIKE 'A%' AND last_name LIKE 'F%'
> - for example.
>
> The above code I've tried for this doesn't seem to work as I'd expect
> it too?
> I've even done
> select * from table where last_name LIKE 'A%' AND LIKE 'F%'
>
> Can anyone provide some details or insights on how to accomplish this?

LIKE A% AND LIKE F%

means "must start with A *AND* must start with F", so the name
"Anderson" would fail because it does start with A, but doesn't start with
F.

Something like

LIKE "A%" OR LIKE "B%" OR LIKE "C%" ... OR LIKE "F%"

would do the trick, but slowly, and it's a pain to write out.


I'd use

BETWEEN 'A' AND 'FZZZ'

(or, to be more precise, >='A' and <'G')



Keep in mind that PostgreSQL is case-sensitive, so if me name were
'Joel deBurton', you wouldn't find me. If you have lower-case starting
names, you'll want to see

(BETWEEN 'A' AND 'FZZZ') OR (BETWEEN 'a' AND 'fzzz')


HTH,
--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington

> What I'd like to do is pull a list of records where there is a range
> of last names; say from A - F.
> select * from table where last_name LIKE 'A%' AND last_name LIKE 'F%'
> - for example.
>
> The above code I've tried for this doesn't seem to work as I'd expect
> it too?

When you use the AND boolean operator, you are asking for records that match
BOTH boolean expressions. And I don't know many words that start with A
*and* F. :)

You want to use the OR operator:

SELECT * FROM table_name WHERE last_name LIKE 'A%' OR last_name LIKE 'F%'

> Can anyone provide some details or insights on how to accomplish this?

If you want a range, you'll have to use a regular expression (or a whole
bunch of LIKE expressions for every value in the range. A regular expression
version would be:

SELECT * FROM table_name WHERE last_name ~ '^[A-F]'

The tilde (~) tells it to match on a regular expression, the carat (^) tells
it to match the beginning of the string, the brackets match a single
character, and the A-F matches one letter in that range.

Hope this helps!

Greg