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 <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
