SQL Where LIKE - Range it!

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

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

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:

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