Обсуждение: Queries

Greetings All.

I have a record on my database with a last name of 'DeFelice'.

I want to be able to retrieve that record whether a user enters 'DeFelice', 'Defelice', 'defelice', or 'DEFELICE'.  Is
therea way I can do that?  Is that what the "ilike" function does? 

I'm on 7.0.3.

Thanks in advance,
Tom Sattler

On Fri, 4 May 2001 tsattler@speakeasy.net wrote:

> Greetings All.
>
> I have a record on my database with a last name of 'DeFelice'.
>
> I want to be able to retrieve that record whether a user enters 'DeFelice', 'Defelice', 'defelice', or 'DEFELICE'.
Isthere a way I can do that?  Is that what the "ilike" function does? 

CREATE INDEX lname_lower ON person ( lower(lname) );
  (above step is optional, but helps performance)

SELECT * FROM person WHERE lower(lname) =  'defelice'

should be faster than ILIKE.

--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington

tsattler@speakeasy.net wrote:
>
> Greetings All.
>
> I have a record on my database with a last name of 'DeFelice'.
>
> I want to be able to retrieve that record whether a user enters 'DeFelice', 'Defelice', 'defelice', or 'DEFELICE'.
Isthere a way I can do that?  Is that what the "ilike" function does? 
>
> I'm on 7.0.3.

~* Match (regex), case insensitive is what you are looking for.

Regards,
Oliver

For a situation like this I have always used the upper() function :

SELECT
    *
FROM
    table_name
WHERE
    upper( last_name ) = 'DEFELICE';

This way, regardless of what is stored in the last_name field, it is forced
to upper before the comparison (it doesn't later what is actually in the
field, so it's safe that way).

I'm not positive, but I believe this is more efficient than RegEx.  The nice
thing about RegEx is it is generic and has many purposes.  The bad thing
about RegEx is that *BECAUSE* it is generic, it does none of those things
particularly efficiently.

Good Luck!!
______________________________________________
John Pagakis - john.pagakis@developonline.com
DevelopOnline.com
(480)377-6828


"Not everyone is going to like what I do, and
 that's something I can accept; if everyone liked
 what I did, I probably wouldn't be playing
 anything of depth."
        -- Joshua Redman

This signature generated by the shareware version of
     ... and I Quote!! Copyright (C) 1999 SpaZmodic Frog Software, Inc.
-----Original Message-----
From: Oliver Vecernik [mailto:vecernik@aon.at]
Sent: Friday, May 04, 2001 2:49 PM
To: tsattler@speakeasy.net
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Queries


tsattler@speakeasy.net wrote:
>
> Greetings All.
>
> I have a record on my database with a last name of 'DeFelice'.
>
> I want to be able to retrieve that record whether a user enters
'DeFelice', 'Defelice', 'defelice', or 'DEFELICE'.  Is there a way I can do
that?  Is that what the "ilike" function does?
>
> I'm on 7.0.3.

~* Match (regex), case insensitive is what you are looking for.

Regards,
Oliver

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly