Pattern Matching - Range of Letters

--- Ron St-Pierre <ron.pgsql@shaw.ca> wrote:

I'm sure that others have solved this but I can't find anything with my
(google and archive) searches. I need to retrieve data where the text
field is within a certain range e.g.
A-An
Am-Bc
Bc-Eg
....
Yi-Zz

Does anyone know of a good approach to achieve this? Should I be looking
into regular expressions, or maybe converting them to their ascii value
first?

Regular expressions would work, but a between statement should work also.

SELECT *
FROM Your_table AS YT
WHERE YT.text_field BETWEEN 'Aa' AND 'An';

Ron St-Pierre wrote:

I'm sure that others have solved this but I can't find anything with my
(google and archive) searches. I need to retrieve data where the text
field is within a certain range e.g.
A-An
Am-Bc
Bc-Eg
....
Yi-Zz

Does anyone know of a good approach to achieve this? Should I be looking
into regular expressions, or maybe converting them to their ascii value
first?

postgres=# create table test (test text);
CREATE TABLE
postgres=# insert into test values ('A');
INSERT 0 1
postgres=# insert into test values ('b');
INSERT 0 1
postgres=# insert into test values ('c');
INSERT 0 1
postgres=# insert into test values ('d');
INSERT 0 1
postgres=# insert into test values ('e');
INSERT 0 1
postgres=# insert into test values ('Ab');
INSERT 0 1
postgres=# insert into test values ('Ac');
INSERT 0 1
postgres=# insert into test values ('Az');
INSERT 0 1
postgres=# select * from test where test between 'A' and 'An';
test
------
A
Ab
Ac
(3 rows)

Any comments are appreciated.

postgres 8.2.4, RHEL

Thanks
Ron St.Pierre

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

Thanks Richard and Joshua, I had no idea that BETWEEN worked for text.

SELECT *
FROM Your_table AS YT
WHERE YT.text_field BETWEEN 'Aa' AND 'An';

postgres=# select * from test where test between 'A' and 'An';
test
------
A
Ab
Ac
(3 rows)

Ron

Ron St-Pierre wrote:

Richard Broersma Jr wrote:

--- Ron St-Pierre <ron.pgsql@shaw.ca> wrote:

I'm sure that others have solved this but I can't find anything
with my
(google and archive) searches. I need to retrieve data where the text
field is within a certain range e.g.
A-An
Am-Bc
Bc-Eg
....

Regular expressions would work, but a between statement should work
also.

SELECT *
FROM Your_table AS YT
WHERE YT.text_field BETWEEN 'Aa' AND 'An';

Ron, in case it's not clear, if an index on text_field exists, the
planner can use it to make such queries run relatively fast.

- John D. Burger
MITRE

That won't work if you have a value "Anz" in there. It would be in the
gap between An and Am.

create table test (test text);
insert into test values ('A');
insert into test values ('b');
insert into test values ('c');
insert into test values ('d');
insert into test values ('e');
insert into test values ('Ab');
insert into test values ('Ac');
insert into test values ('Amz');
insert into test values ('Az');

select * from test where test between 'A' and 'Am';
"A"
"Ab"
"Ac"

select * from test where test between 'An' and 'Bc';
"Az"

I wouldn't use between in this case. I'd suggest this:
select * from test where test >= 'A' and test <'Am';
"A"
"Ab"
"Ac"

select * from test where test >= 'Am' and test <'Bc';
"Amz"
"Az"

The end will be tricky because ""zzzz is not < "zz" so you will need the
last select to be

select * from test where test >= 'Yi';

The beginning will be tricky too if you allow things that come before A
such as 0-9 or spaces.

Richard Broersma Jr wrote:

William Garrison wrote:

That won't work if you have a value "Anz" in there. It would be in
the gap between An and Am.

Yes, I realized that too. My solution to it is a bit of a hack, but it's
easy and it works for me in this case. I translate everything to
uppercase and simply append 'ZZZZZZ' to the end of the second string.
None of the strings I am comparing to are longer than 6 characters, and
there are no numerical values in them.

Ron