Обсуждение: Pattern Matching - Range of Letters
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? Any comments are appreciated. postgres 8.2.4, RHEL Thanks Ron St.Pierre
--- 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/
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
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: > 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? > > 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 >
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:
> --- 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';
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
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
>
> 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:
>> --- 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';
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: Don't 'kill -9' the postmaster
>>
>
>