Horton

Thread

  1. Re: [SQL] How to Make Case InSensitive???

    Barracuda <cuda_fx@yahoo.com> — 1998-09-16T13:48:10Z

    thank you for this tip!  don't know what the ^ or the $ is used for
but the ~* and the \do helped me out a great deal.  No info on this in
any documentation.

Charles




---David Hartwig <daveh@insightdist.com> wrote:
>
> 
> 
> Marcio Macedo wrote:
> 
> > Hey...
> >
> > How does this " ~* " operator works ?!?!?!
> >
> > David Hartwig wrote:
> > >
> > >
> > >      SELECT * FROM series WHERE upper(NAME) = 'INDIA'
> > >                             or
> > >      SELECT * FROM series WHERE NAME ~* '^india$'
> 
> >
> 
> I don't use it myself, but it is a case insensitive regular expression
> match operator.    "~' is the case sensitive operator.    Do "\do" in
> psql to see all the operators.
> 
> 
> 

_________________________________________________________
DO YOU YAHOO!?
Get your free @yahoo.com address at http://mail.yahoo.com

  2. Re[2]: [SQL] How to Make Case InSensitive???

    jose' soares <sferac@bo.nettuno.it> — 1998-09-16T14:14:20Z

    Hello Barracuda,

mercoledì, 16 settembre 98, you wrote:

B> thank you for this tip!  don't know what the ^ or the $ is used for

regular expression operators ^ stand for first character and $ stand
for last character:

    ~* '^india$'

matches  India
         india
         INDIA
         
but not  Indiana
         New India

>> 
B> but the ~* and the \do helped me out a great deal.  No info on this in
B> any documentation.

Take a look at PostgreSQL User's Guide, Chapter 5, there's some
information about PostgreSQL operators.

B> Charles




B> ---David Hartwig <daveh@insightdist.com> wrote:
>>
>> 
>> 
>> Marcio Macedo wrote:
>> 
>> > Hey...
>> >
>> > How does this " ~* " operator works ?!?!?!
>> >
>> > David Hartwig wrote:
>> > >
>> > >
>> > >      SELECT * FROM series WHERE upper(NAME) = 'INDIA'
>> > >                             or
>> > >      SELECT * FROM series WHERE NAME ~* '^india$'
>> 
>> >
>> 
>> I don't use it myself, but it is a case insensitive regular expression
>> match operator.    "~' is the case sensitive operator.    Do "\do" in
>> psql to see all the operators.
>> 
>> 
>> 

B> _________________________________________________________
B> DO YOU YAHOO!?
B> Get your free @yahoo.com address at http://mail.yahoo.com

 Jose'

  3. Re[2]: [SQL] How to Make Case InSensitive???

    Herouth Maoz <herouth@oumail.openu.ac.il> — 1998-09-16T14:42:36Z

    At 16:14 +0200 on 16/9/98, Sferacarta Software wrote:


> regular expression operators ^ stand for first character and $ stand
> for last character:
>
>     ~* '^india$'
>
> matches  India
>          india
>          INDIA
>
> but not  Indiana
>          New India

Another way to think of it is like this:

'india'      is eqivalent to       "contains 'india'"
'^india'     is eqivalent to       "starts with 'india'"
'india$'     is eqivalent to       "ends with 'india'"
'^india$'    is eqivalent to       "is exactly 'india'"

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

  4. Re: [SQL] How to Make Case InSensitive???

    Bruce Momjian <maillist@candle.pha.pa.us> — 1998-09-16T17:30:47Z

    > thank you for this tip!  don't know what the ^ or the $ is used for
> but the ~* and the \do helped me out a great deal.  No info on this in
> any documentation.
> 
> Charles

Actually, the FAQ mentions it, the psql \? command, and psql manual page
mention it.  Is there somewhere else we should mention it?

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
http://www.op.net/~candle              |  (610) 353-9879(w)
  +  If your life is a hard drive,     |  (610) 853-3000(h)
  +  Christ can be your backup.        |

  5. Re: [SQL] How to Make Case InSensitive???

    Jérome Knöbl <jknobl@mandanet.ch> — 1998-09-17T08:13:51Z

    I working with a table of about 70'000 records and I made 2 tests about
the optimisation of ~* :

With ~* :
----------
% time -p psql mayer98 -c "select * from artistes where nom ~* 'picasso';"

  ref|nom                         |nom_court    |type|nationalite
-----+----------------------------+-------------+----+-----------
76160|PICASSO Pablo, 1881-1973    |PICASSO      |   0|E
76161|PICASSO Loulou, *1958       |PICASSOLoulou|   0|F
76162|(A) PICASSO Pablo, 1881-1973|PICASSOPablo |   6|E
(3 rows)

real 11.52 *
user 0.02
sys 0.01

With upper(...) and like upper(%...%)
--------------------------------------
% time -p psql mayer98 -c "select * from artistes where upper(nom) like
upper('%PICASSO%');"
  ref|nom                         |nom_court    |type|nationalite
-----+----------------------------+-------------+----+-----------
76160|PICASSO Pablo, 1881-1973    |PICASSO      |   0|E
76161|PICASSO Loulou, *1958       |PICASSOLoulou|   0|F
76162|(A) PICASSO Pablo, 1881-1973|PICASSOPablo |   6|E
(3 rows)

real 4.44 *
user 0.03
sys 0.00

Relsult: it seem to be 2.6 time more optimised to use upper(...) like
upper('%...%') ???

JK


Herouth Maoz wrote:

> At 16:14 +0200 on 16/9/98, Sferacarta Software wrote:
>
> > regular expression operators ^ stand for first character and $ stand
> > for last character:
> >
> >     ~* '^india$'
> >
> > matches  India
> >          india
> >          INDIA
> >
> > but not  Indiana
> >          New India
>
> Another way to think of it is like this:
>
> 'india'      is eqivalent to       "contains 'india'"
> '^india'     is eqivalent to       "starts with 'india'"
> 'india$'     is eqivalent to       "ends with 'india'"
> '^india$'    is eqivalent to       "is exactly 'india'"
>
> Herouth
>
> --
> Herouth Maoz, Internet developer.
> Open University of Israel - Telem project
> http://telem.openu.ac.il/~herutma

  6. Re: [SQL] How to Make Case InSensitive???

    Leslie Mikesell <les@mcs.net> — 1998-09-19T22:48:50Z

    According to Bruce Momjian:
> 
> > thank you for this tip!  don't know what the ^ or the $ is used for
> > but the ~* and the \do helped me out a great deal.  No info on this in
> > any documentation.
> > 
> > Charles
> 
> Actually, the FAQ mentions it, the psql \? command, and psql manual page
> mention it.  Is there somewhere else we should mention it?

Is it documented which things work with which data types (text vs. varchar)?

  Les Mikesell
    les@mcs.com

  7. Re: [SQL] How to Make Case InSensitive???

    Bruce Momjian <maillist@candle.pha.pa.us> — 1998-09-21T01:39:33Z

    > I working with a table of about 70'000 records and I made 2 tests about
> the optimisation of ~* :
> 
> With ~* :
> ----------
> % time -p psql mayer98 -c "select * from artistes where nom ~* 'picasso';"
> 
>   ref|nom                         |nom_court    |type|nationalite
> -----+----------------------------+-------------+----+-----------
> 76160|PICASSO Pablo, 1881-1973    |PICASSO      |   0|E
> 76161|PICASSO Loulou, *1958       |PICASSOLoulou|   0|F
> 76162|(A) PICASSO Pablo, 1881-1973|PICASSOPablo |   6|E
> (3 rows)
> 
> real 11.52 *
> user 0.02
> sys 0.01
> 
> With upper(...) and like upper(%...%)
> --------------------------------------
> % time -p psql mayer98 -c "select * from artistes where upper(nom) like
> upper('%PICASSO%');"
>   ref|nom                         |nom_court    |type|nationalite
> -----+----------------------------+-------------+----+-----------
> 76160|PICASSO Pablo, 1881-1973    |PICASSO      |   0|E
> 76161|PICASSO Loulou, *1958       |PICASSOLoulou|   0|F
> 76162|(A) PICASSO Pablo, 1881-1973|PICASSOPablo |   6|E
> (3 rows)
> 
> real 4.44 *
> user 0.03
> sys 0.00
> 
> Relsult: it seem to be 2.6 time more optimised to use upper(...) like
> upper('%...%') ???
> 
> JK
> 

This is interesting.  Does anyone have an idea why this is happening? 
We do need a faster regular expression library, and I am hoping the
original author, Henry Spencer, will complete the rewrite he is doing
soon.


-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
http://www.op.net/~candle              |  (610) 353-9879(w)
  +  If your life is a hard drive,     |  (610) 853-3000(h)
  +  Christ can be your backup.        |