Re: Pattern Matching - Range of Letters

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(dot)pgsql(at)shaw(dot)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
>>
>
>