[mod_python] String format in "LIKE" clause

Luis M. Gonzalez wrote ..
> Thanks Graham, but no, this is not what I want to do...
> I'll try to explain it better:
> 
> Lets say that I want to return a list of companies whose names include
> the 
> word "Co" ("Co" is pased as a parameter to the query), so I can get:
> 
> Acme Corporation Ltd.
> Barnes Corp.  Inc.
> Corporative Management S.A.
> Fresh Corpse Foods, Ltd.
> etc, etc...
> 
> So, how should I include this parameter into the LIKE clause.
> I guess it should be enclosed between "%" and "%", but what should I put
> in 
> between?

Ultimately you use the substring of string you are looking for.

Quoting from a Google search, as that way it looks authoritative:

  The LIKE pattern matching operator can also be used in the conditional selection
  of the where clause. Like is a very powerful operator that allows you to select
  only rows that are "like" what you specify. The percent sign "%" can be used as
  a wild card to match any possible character that might appear before or after the
  characters specified. For example:

  select first, last, city
     from empinfo
     where first LIKE 'Er%';
 
What you were doing is basically correct, although you were missing single quotes.

The real issue as I pointed out was that because you were using '%' operator to
compose the string, you need to have "%%" in the string where you want a real '%'.

Adding the missing quotes:

  "select * from customers where company like '%%%s%%'"  % "Co"

Which yields an actual SQL string of:

  "select * from customers where company like '%Co%'"

BTW, if the substring you want to search for is coming from a web page or other
user input this could be dangerous. For example, they could include a single quote
in the substring for searching and stuff it all up. Thus, you need to perform
appropriate escaping to avoid such problems.

Graham

> ----- Original Message ----- 
> From: "Graham Dumpleton" <grahamd at dscpl.com.au>
> To: "Luis M. Gonzalez" <luismg at gmx.net>
> Cc: <mod_python at modpython.org>
> Sent: Thursday, January 26, 2006 8:46 PM
> Subject: Re: [mod_python] String format in "LIKE" clause
> 
> 
> > Luis M. Gonzalez wrote ..
> >> I tried this:
> >> ("select * from customers where company like %s%"  %company ) # doesn't
> >> work
> >> ("select * from customers where company like '%(%s)%"  %company  # 
> >> doesn't
> >> work either
> >>
> >> The question is:
> >> How should a include the parameter into de LIKE clause in order to accept
> >> only a part of the full company name and return the matching results?
> >
> > Double up the"%" to get an actual percentage passed through.
> >
> >  ("select * from customers where company like %s%%"  %company )
> >
> > At least that would be the problem if it is simply using "%" operator
> on
> > string:
> >
> >>>> "select * from customers where company like %s%" % "ME"
> > Traceback (most recent call last):
> >  File "<stdin>", line 1, in ?
> > ValueError: incomplete format
> >
> >>>> "select * from customers where company like %s%%"  % "ME"
> > 'select * from customers where company like ME%'
> >
> > Graham
> >