pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [SQL] How to Make Case InSensitive???
11+ messages / 8 participants
[nested] [flat]
* Re: [SQL] How to Make Case InSensitive???
@ 1998-09-01 14:14 Marcio Macedo <marciom@conectiva.com.br>
parent: David Hartwig <daveh@insightdist.com>
0 siblings, 1 reply; 11+ messages in thread
From: Marcio Macedo @ 1998-09-01 14:14 UTC (permalink / raw)
To: David Hartwig <daveh@insightdist.com>; +Cc: S.Ramaswamy <srswamy@giasdl01.vsnl.net.in>; pgsql-sql
Hey...
How does this " ~* " operator works ?!?!?!
David Hartwig wrote:
>
>
> SELECT * FROM series WHERE upper(NAME) = 'INDIA'
> or
> SELECT * FROM series WHERE NAME ~* '^india$'
--Marcio Macedo
Conectiva
^ permalink raw reply [nested|flat] 11+ messages in thread
* How to Make Case InSensitive???
@ 1998-09-01 16:02 S.Ramaswamy <srswamy@giasdl01.vsnl.net.in>
0 siblings, 1 reply; 11+ messages in thread
From: S.Ramaswamy @ 1998-09-01 16:02 UTC (permalink / raw)
To: pgsql-sql
When I use SQL statements, how can I make it case insensitive. If I
use
SELECT * FROM series WHERE NAME='India'
I want to select rows including those where NAME='INDIA' / NAME =
'india'
and so on.
Can someone help me in the above??
Regards
S.Ramaswamy
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] How to Make Case InSensitive???
@ 1998-09-01 16:21 David Hartwig <daveh@insightdist.com>
parent: S.Ramaswamy <srswamy@giasdl01.vsnl.net.in>
0 siblings, 1 reply; 11+ messages in thread
From: David Hartwig @ 1998-09-01 16:21 UTC (permalink / raw)
To: S.Ramaswamy <srswamy@giasdl01.vsnl.net.in>; +Cc: pgsql-sql
S.Ramaswamy wrote:
> When I use SQL statements, how can I make it case insensitive. If I
> use
>
> SELECT * FROM series WHERE NAME='India'
>
> I want to select rows including those where NAME='INDIA' / NAME =
> 'india'
> and so on.
>
> Can someone help me in the above??
SELECT * FROM series WHERE upper(NAME) = 'INDIA'
or
SELECT * FROM series WHERE NAME ~* '^india$'
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] How to Make Case InSensitive???
@ 1998-09-01 17:47 David Hartwig <daveh@insightdist.com>
parent: Marcio Macedo <marciom@conectiva.com.br>
0 siblings, 0 replies; 11+ messages in thread
From: David Hartwig @ 1998-09-01 17:47 UTC (permalink / raw)
To: Marcio Macedo <marciom@conectiva.com.br>; +Cc: S.Ramaswamy <srswamy@giasdl01.vsnl.net.in>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] How to Make Case InSensitive???
@ 1998-09-16 13:48 Barracuda <cuda_fx@yahoo.com>
0 siblings, 2 replies; 11+ messages in thread
From: Barracuda @ 1998-09-16 13:48 UTC (permalink / raw)
To: Marcio Macedo <marciom@conectiva.com.br>; David Hartwig <daveh@insightdist.com>; +Cc: pgsql-sql@postgreSQL.org, "S.Ramaswamy" <srswamy@giasdl01.vsnl.net.in>
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] How to Make Case InSensitive???
@ 1998-09-16 17:30 Bruce Momjian <maillist@candle.pha.pa.us>
parent: Barracuda <cuda_fx@yahoo.com>
1 sibling, 1 reply; 11+ messages in thread
From: Bruce Momjian @ 1998-09-16 17:30 UTC (permalink / raw)
To: cuda_fx@yahoo.com; +Cc: marciom@conectiva.com.br; daveh@insightdist.com; pgsql-sql; srswamy@giasdl01.vsnl.net.in
> 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. |
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] How to Make Case InSensitive???
@ 1998-09-17 08:13 Jerome Knobl <jknobl@mandanet.ch>
parent: Barracuda <cuda_fx@yahoo.com>
1 sibling, 1 reply; 11+ messages in thread
From: Jerome Knobl @ 1998-09-17 08:13 UTC (permalink / raw)
To: Herouth Maoz <herouth@oumail.openu.ac.il>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] How to Make Case InSensitive???
@ 1998-09-19 22:48 Leslie Mikesell <les@Mcs.Net>
parent: Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 0 replies; 11+ messages in thread
From: Leslie Mikesell @ 1998-09-19 22:48 UTC (permalink / raw)
To: maillist@candle.pha.pa.us; +Cc: cuda_fx@yahoo.com; marciom@conectiva.com.br; daveh@insightdist.com; pgsql-sql; srswamy@giasdl01.vsnl.net.in
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] How to Make Case InSensitive???
@ 1998-09-21 01:39 Bruce Momjian <maillist@candle.pha.pa.us>
parent: Jerome Knobl <jknobl@mandanet.ch>
0 siblings, 0 replies; 11+ messages in thread
From: Bruce Momjian @ 1998-09-21 01:39 UTC (permalink / raw)
To: jknobl@mandanet.ch; +Cc: herouth@oumail.openu.ac.il; pgsql-sql; henry@zoo.utoronto.ca
> 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. |
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] How to Make Case InSensitive???
@ 1998-09-21 19:07 Mirek Budzanowski <mirekb@tcs.uni.wroc.pl>
0 siblings, 1 reply; 11+ messages in thread
From: Mirek Budzanowski @ 1998-09-21 19:07 UTC (permalink / raw)
To: pgsql-sql
> > I working with a table of about 70'000 records and I made 2 tests about
> > the optimisation of ~* :
> >
> > With ~* :
> > real 11.52 *
> > user 0.02
> > sys 0.01
> >
> > With upper(...) and like upper(%...%)
> > --------------------------------------
> > real 4.44 *
> > user 0.03
> > sys 0.00
> >
> > Relsult: it seem to be 2.6 time more optimised to use upper(...) like
> > upper('%...%') ???
> This is interesting. Does anyone have an idea why this is happening?
In my opinion the reason why regular expresion matching is sloer is
obvious. Regulars expresion are very powerfull, but the code that
does the matching is a bit more complicated. With like expresions
you may write simple and faster code.
Mirek
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] How to Make Case InSensitive???
@ 1998-09-22 16:47 Bruce Momjian <maillist@candle.pha.pa.us>
parent: Mirek Budzanowski <mirekb@tcs.uni.wroc.pl>
0 siblings, 0 replies; 11+ messages in thread
From: Bruce Momjian @ 1998-09-22 16:47 UTC (permalink / raw)
To: mirekb@tcs.uni.wroc.pl; +Cc: pgsql-sql
[Charset ISO-8859-2 unsupported, filtering to ASCII...]
>
> > > I working with a table of about 70'000 records and I made 2 tests about
> > > the optimisation of ~* :
> > >
> > > With ~* :
>
> > > real 11.52 *
> > > user 0.02
> > > sys 0.01
> > >
> > > With upper(...) and like upper(%...%)
> > > --------------------------------------
>
> > > real 4.44 *
> > > user 0.03
> > > sys 0.00
> > >
> > > Relsult: it seem to be 2.6 time more optimised to use upper(...) like
> > > upper('%...%') ???
>
> > This is interesting. Does anyone have an idea why this is happening?
>
> In my opinion the reason why regular expresion matching is sloer is
> obvious. Regulars expresion are very powerfull, but the code that
> does the matching is a bit more complicated. With like expresions
> you may write simple and faster code.
Makes sense.
--
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. |
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~1998-09-22 16:47 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-09-01 16:02 How to Make Case InSensitive??? S.Ramaswamy <srswamy@giasdl01.vsnl.net.in>
1998-09-01 16:21 ` David Hartwig <daveh@insightdist.com>
1998-09-01 14:14 ` Marcio Macedo <marciom@conectiva.com.br>
1998-09-01 17:47 ` David Hartwig <daveh@insightdist.com>
1998-09-16 13:48 Re: [SQL] How to Make Case InSensitive??? Barracuda <cuda_fx@yahoo.com>
1998-09-16 17:30 ` Bruce Momjian <maillist@candle.pha.pa.us>
1998-09-19 22:48 ` Leslie Mikesell <les@Mcs.Net>
1998-09-17 08:13 ` Jerome Knobl <jknobl@mandanet.ch>
1998-09-21 01:39 ` Bruce Momjian <maillist@candle.pha.pa.us>
1998-09-21 19:07 Re: [SQL] How to Make Case InSensitive??? Mirek Budzanowski <mirekb@tcs.uni.wroc.pl>
1998-09-22 16:47 ` Bruce Momjian <maillist@candle.pha.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox