Re: Re: SQL Where Like - Range it?!

On Sat, Apr 28, 2001 at 10:53:08AM +0800, Lincoln Yeoh wrote:
> At 04:00 PM 27-04-2001 -0500, will trillich wrote:
> >On Fri, Apr 27, 2001 at 09:52:26AM -0500, Len Morgan wrote:
> >>
> >> SELECT * FROM table WHERE last_name BETWEEN 'A' AND 'Fzzzzzzzzzzzzz' ;
> >> worked for me.
> >>
> >> You could also use BETWEEN 'A' AND 'G' to avoid all of the zzzzzzzzs at the
> >> end.  Crude but effective.
> >
> >how about regex?
> >
> >    select * from tbl where fld ~ '^[A-F]';
>
> Would the regex query still use a basic text index?

apparently it does IF you use the 'anchor-at-beginning'
construct, namely the "^":

    fld ~ '^[A-F]' -- STARTS with A,B,C,D,E, or F
    fld ~ '[A-F]'  -- merely contains A,B,C,D,E, or F
    fld ~ '[A-F]$' -- ENDS with A-F

if starts-with (^) then it uses the index. so i hear.

> If you're using US-ASCII encoding you could avoid the zzzzs by using a '~'

that condition should let you know that you're hard-coding
something that you probably shouldn't, if you're interested in
portability...

> e.g.
> SELECT * FROM table where last_name >='A' and last_name <='F~';

eek. avoid. breaks on ebcdic, maybe unicode, possibly certain
latin implementations....

> If not use a < 'G' instead of a <=.

much wiser...

--
don't visit this page. it's bad for you. take my expert word for it.
http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!