Thread: select LIKE
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 --------------------------------------------------------------------
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.
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 --------------------------------------------------------------------
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.
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 --------------------------------------------------------------------
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
> --------------------------------------------------------------------
>
>
>
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 --------------------------------------------------------------------
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
> 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