[mod_python] String format in "LIKE" clause ( SOLVED! )

Thank you Graham!!
That works now! Adding double percent signs instead of single ones solved 
the problem.

Thanks again and best regards,
Luis



----- 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 9:47 PM
Subject: Re: [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
>> >
>