Postgres Pro
Downloads
Home   >   mailing lists

Thread: Case insensitive string comparison?

Case insensitive string comparison?

From
Borek Lupoměský
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
  Is there an operator for case insensitive string comparison, or
should I use regular expression matching with ~* '^string$'?
  Bye Borek

- -- 

=====================================================================
BOREK LUPOMESKY                Usti nad Labem, Czech Republic, Europe
WWW: http://www.volny.cz/borekl/                  PGP keyid: B6A06AEB
==========[ MIME/ISO-8859-2 & PGP encrypted mail welcome ]===========

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.1 (GNU/Linux)
Comment: Get GPG key at http://www.volny.cz/borekl/pgp.txt

iD8DBQE7ASWbA6dWI7agausRAj4JAKCk5MCYlsLEjf+IR5ZZfy6ypDNG7QCeJj2n
eyPHuxTW+WQGyeo4SuvZvsw=
=p+pz
-----END PGP SIGNATURE-----

Re: Case insensitive string comparison?

From
Karel Zak
Date:
On Tue, May 15, 2001 at 02:48:24PM +0200, Borek Lupoměský wrote:
>    Is there an operator for case insensitive string comparison, or
> should I use regular expression matching with ~* '^string$'?
possibility:
- use upper() / lower() inside query
- regex operators: ~* or !~* 
 - case insensitive 'like'
        Karel

-- Karel Zak  <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz,
http://mape.jcu.cz

Re: Case insensitive string comparison?

From
Cedar Cox
Date:
On Tue, 15 May 2001, Karel Zak wrote:

> On Tue, May 15, 2001 at 02:48:24PM +0200, Borek Lupomìský wrote:
> >    Is there an operator for case insensitive string comparison, or
> > should I use regular expression matching with ~* '^string$'?
> 
>  possibility:
> 
>     - use upper() / lower() inside query

Ok, this is probably a really silly question:  which is faster?
I expect they are nearly identical code.  Just wondering...

-Cedar

Re: Case insensitive string comparison?

From
Karel Zak
Date:
On Mon, May 21, 2001 at 12:15:41PM +0300, Cedar Cox wrote:
> 
> On Tue, 15 May 2001, Karel Zak wrote:
> 
> > On Tue, May 15, 2001 at 02:48:24PM +0200, Borek Lupom?ský wrote:
> > >    Is there an operator for case insensitive string comparison, or
> > > should I use regular expression matching with ~* '^string$'?
> > 
> >  possibility:
> > 
> >     - use upper() / lower() inside query
> 
> Ok, this is probably a really silly question:  which is faster?
> I expect they are nearly identical code.  Just wondering...
> 

Only one answer will be right ... try all possibility :-)


IMHO the 'ILIKE' is the most fast, because it's simple for parser and
it's one function.

            Karel

-- Karel Zak  <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz,
http://mape.jcu.cz

Re: Case insensitive string comparison?

From
"tjk@tksoft.com"
Date:
You can index the lower(...) queries, but you can't for the
regex queries.


Troy

> 
> 
> On Tue, 15 May 2001, Karel Zak wrote:
> 
> > On Tue, May 15, 2001 at 02:48:24PM +0200, Borek Lupomìský wrote:
> > >    Is there an operator for case insensitive string comparison, or
> > > should I use regular expression matching with ~* '^string$'?
> > 
> >  possibility:
> > 
> >     - use upper() / lower() inside query
> 
> Ok, this is probably a really silly question:  which is faster?
> I expect they are nearly identical code.  Just wondering...
> 
> -Cedar
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
>