Re: [GENERAL] select LIKE - Mailing list pgsql-general

From David Hartwig
Subject Re: [GENERAL] select LIKE
Date
Msg-id 35EF2C00.834644F0@bellatlantic.net
Whole thread Raw
In response to select LIKE  (Kevin Heflin <kheflin@shreve.net>)
Responses Re: [GENERAL] select LIKE
List pgsql-general

Kevin Heflin wrote:

> I have a rolodex database in Postgres.
> I want allow searches by "name" and/or company from a web page.
> I'm using PHP/Apache to connect with postgres
>
> I tried something like:
>
> select * from rolodex where name LIKE '%$name%'
>
> First question: is there a way to do this that is NOT case sensitive?
>

Use the case insensitive regular expression operator.

    select * from rolodex where name ~* '.*$name.*'

I'm not sure if you need either of the ".*" wild card pairs to match zero or
more of any character.

>
> also I wanted to allow for searches of name and/or company..
>
> but if a user enters info in "name" and not "company" it returns back no
> items found.
>
> I'm using '%$company%' (percent signs on both sides so that the user can
> take a short guess. However if the user doesn't input anything for
> company, this returns no items.. if I have the variable set up as:
>
> '%$company' with only the percent sign in the beginning, it will work o.k.

I'm not sure what LIKE '%%' means.  It may be taken as a literal "%" and not
as a wild card.  Try removing the entire condition if $company is null.  You
will get better performance, if it matters.


pgsql-general by date:

Previous
From: Kevin Heflin
Date:
Subject: select LIKE
Next
From: Bruce Momjian
Date:
Subject: Re: [GENERAL] big numbers