Re: SQL Where LIKE - Range it!

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(at)scw(dot)org>
Director of Information Systems, Support Center of Washington