Thread: select LIKE

select LIKE

From
Kevin Heflin
Date:
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?

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.

Any suggestions would be appreciated.

Kevin




--------------------------------------------------------------------
Kevin Heflin          | ShreveNet, Inc.      | Ph:318.222.2638 x103
VP/Mac Tech           | 333 Texas St #619    | FAX:318.221.6612
kheflin@shreve.net    | Shreveport, LA 71101 | http://www.shreve.net
--------------------------------------------------------------------


Re: [GENERAL] select LIKE

From
David Hartwig
Date:

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.


Re: [GENERAL] select LIKE

From
Kevin Heflin
Date:
On Thu, 3 Sep 1998, David Hartwig wrote:

> > 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.*'


Thanks! that solved both my problems.

Is this regular SQL or something more specific to PostgreSQL ?


The SQL book I looked through didn't show anything like this ~*

Kevin



--------------------------------------------------------------------
Kevin Heflin          | ShreveNet, Inc.      | Ph:318.222.2638 x103
VP/Mac Tech           | 333 Texas St #619    | FAX:318.221.6612
kheflin@shreve.net    | Shreveport, LA 71101 | http://www.shreve.net
--------------------------------------------------------------------


Re: [GENERAL] select LIKE

From
David Hartwig
Date:

Kevin Heflin wrote:

> On Thu, 3 Sep 1998, David Hartwig wrote:
>
> > > 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.*'
>
> Thanks! that solved both my problems.
>
> Is this regular SQL or something more specific to PostgreSQL ?
>
> The SQL book I looked through didn't show anything like this ~*
>

This is what happens when you put SQL source code in the hand of a bunch of
Unix hacks.   I am mot aware of this operator in any other dialect of SQL.

BTW, do a "\do" in psql to get a list of operators.  You may see a few that
may be interesting.


alter table ?

From
Kevin Heflin
Date:
I went to alter a table, to add a column like so:

alter table passwd add column spamblock bool DEFAULT 'TRUE'

this added the new column of type bool but did not make the default true.

Any suggestions?

Kevin


--------------------------------------------------------------------
Kevin Heflin          | ShreveNet, Inc.      | Ph:318.222.2638 x103
VP/Mac Tech           | 333 Texas St #619    | FAX:318.221.6612
kheflin@shreve.net    | Shreveport, LA 71101 | http://www.shreve.net
--------------------------------------------------------------------


Re: [GENERAL] alter table ?

From
Postgres DBA
Date:
Yeah, that's true, but you can simply overcome this problem with following
query that you should issue right after "alter" query:
    update passwd set spamblock='TRUE';

Aleksey.

On Thu, 12 Nov 1998, Kevin Heflin wrote:

>
> I went to alter a table, to add a column like so:
>
> alter table passwd add column spamblock bool DEFAULT 'TRUE'
>
> this added the new column of type bool but did not make the default true.
>
> Any suggestions?
>
> Kevin
>
>
> --------------------------------------------------------------------
> Kevin Heflin          | ShreveNet, Inc.      | Ph:318.222.2638 x103
> VP/Mac Tech           | 333 Texas St #619    | FAX:318.221.6612
> kheflin@shreve.net    | Shreveport, LA 71101 | http://www.shreve.net
> --------------------------------------------------------------------
>
>
>


Re: [GENERAL] alter table ?

From
Kevin Heflin
Date:
On Fri, 13 Nov 1998, Postgres DBA wrote:

> Yeah, that's true, but you can simply overcome this problem with following
> query that you should issue right after "alter" query:
>     update passwd set spamblock='TRUE';


Yea, I did that, but I guess what I'm looking for, was that when ever
anything was added to the table. Regardless of whether or not the
spamblock was specified, it would be set to 'TRUE'


Kevin


--------------------------------------------------------------------
Kevin Heflin          | ShreveNet, Inc.      | Ph:318.222.2638 x103
VP/Mac Tech           | 333 Texas St #619    | FAX:318.221.6612
kheflin@shreve.net    | Shreveport, LA 71101 | http://www.shreve.net
--------------------------------------------------------------------


Re: [GENERAL] alter table ?

From
Herouth Maoz
Date:
At 12:18 +0200 on 13/11/98, Kevin Heflin wrote:


> Yea, I did that, but I guess what I'm looking for, was that when ever
> anything was added to the table. Regardless of whether or not the
> spamblock was specified, it would be set to 'TRUE'

I guess adding constraints in alter statement is something to be placed in
Postgres's todo list if it is not there already.

But personally, I prefer doing this with "definition file" - SQL files
which define all the tables in my database.

In case of alteration, I dump all my tables and sequences, re-run the
creation file (which drops all tables and redefines everything), and reload
the tables and sequences - possibly adding data within the dump files to
reflect the addition or removal of columns.

This way, you can always recreate your database, create a second mirror, a
production database, etc. - and you can fill the "definition file" with
comments which will help you understand what you meant a year afterwards.

It's a tradition I kept from the time we used Oracle in one of my former
jobs. I think it's a practice taught in basic Oracle training, but it's
good for any database (that supports SQL/DDL scripts).

Herouth

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



Re: [GENERAL] alter table ?

From
Bruce Momjian
Date:
> At 12:18 +0200 on 13/11/98, Kevin Heflin wrote:
>
>
> > Yea, I did that, but I guess what I'm looking for, was that when ever
> > anything was added to the table. Regardless of whether or not the
> > spamblock was specified, it would be set to 'TRUE'
>
> I guess adding constraints in alter statement is something to be placed in
> Postgres's todo list if it is not there already.


Added to TODO.



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