string formatting using the % operator

[William Gill]

I am using the % operator to create queries for a db app. It works fine
when exact strings, or numbers are used, but some queries need partial
matching that use the '%' as a wildcards. So for example the resultant
string should be 'WHERE name LIKE %smith%' (would match silversmith,
smithy, and smith). Is there any way to get something like

searchterm = 'smith'
sql += 'WHERE name LIKE %s' % searchterm

to return 'WHERE name LIKE %smith%' I have tried using escapes,
character codes for the % sign, and lots of other gyrations with no
success. The only thing that works is if I modify searchterm first:

searchterm = 'smith'
searchterm ='%'+'smith'+'%'
sql += 'WHERE name LIKE %s' % searchterm

Any Ideas?

Thanks,
Bill

[fargo]

William Gill wrote:

I am using the % operator to create queries for a db app. It works fine
when exact strings, or numbers are used, but some queries need partial
matching that use the '%' as a wildcards. So for example the resultant
string should be 'WHERE name LIKE %smith%' (would match silversmith,
smithy, and smith). Is there any way to get something like

searchterm = 'smith'
sql += 'WHERE name LIKE %s' % searchterm

to return 'WHERE name LIKE %smith%' I have tried using escapes,
character codes for the % sign, and lots of other gyrations with no
success. The only thing that works is if I modify searchterm first:

searchterm = 'smith'
searchterm ='%'+'smith'+'%'
sql += 'WHERE name LIKE %s' % searchterm

Any Ideas?

try this :

sql += 'WHERE name LIKE %%%s%%' % searchterm

[harold fellermann]

> to return 'WHERE name LIKE %smith%' I have tried using escapes,

character codes for the % sign, and lots of other gyrations with no
success. The only thing that works is if I modify searchterm first:

searchterm = 'smith'
searchterm ='%'+'smith'+'%'
sql += 'WHERE name LIKE %s' % searchterm

Any Ideas?

"%%%s%%" % "here you go"

'%here you go%'

Cheers,

- harold -

--
If your only tool is a hammer, every problem looks like a nail.
--

[Dan Sommers]

On Mon, 13 Jun 2005 15:12:54 GMT,
William Gill <no*****@gcgroup.net> wrote:

I am using the % operator to create queries for a db app. It works fine
when exact strings, or numbers are used, but some queries need partial
matching that use the '%' as a wildcards. So for example the resultant
string should be 'WHERE name LIKE %smith%' (would match silversmith,
smithy, and smith). Is there any way to get something like searchterm = 'smith'
sql += 'WHERE name LIKE %s' % searchterm to return 'WHERE name LIKE %smith%' I have tried using escapes,
character codes for the % sign, and lots of other gyrations with no
success. The only thing that works is if I modify searchterm first: searchterm = 'smith'
searchterm ='%'+'smith'+'%'
sql += 'WHERE name LIKE %s' % searchterm Any Ideas?

Let the DB-API do more work for you:

cursor = connection.cursor( )
sql = """SELECT column2, columns3 FROM table WHERE name LIKE %s"""
values = ('%%%s%%' % searchterm,) # note that this is a tuple
cursor.execute( sql, values )

HTH,
Dan

--
Dan Sommers
<http://www.tombstonezero.net/dan/>

[William Gill]

Dan Sommers wrote:

On Mon, 13 Jun 2005 15:12:54 GMT,
William Gill <no*****@gcgroup.net> wrote:

I am using the % operator to create queries for a db app. It works fine
when exact strings, or numbers are used, but some queries need partial
matching that use the '%' as a wildcards. So for example the resultant
string should be 'WHERE name LIKE %smith%' (would match silversmith,
smithy, and smith). Is there any way to get something like

searchterm = 'smith'
sql += 'WHERE name LIKE %s' % searchterm

to return 'WHERE name LIKE %smith%' I have tried using escapes,
character codes for the % sign, and lots of other gyrations with no
success. The only thing that works is if I modify searchterm first:

searchterm = 'smith'
searchterm ='%'+'smith'+'%'
sql += 'WHERE name LIKE %s' % searchterm

Any Ideas?

Let the DB-API do more work for you:

cursor = connection.cursor( )
sql = """SELECT column2, columns3 FROM table WHERE name LIKE %s"""
values = ('%%%s%%' % searchterm,) # note that this is a tuple
cursor.execute( sql, values )

HTH,
Dan

I can't tell you how many times I looked at the table of format codes
and missed this.

Thanks everyone!

[Peter Hansen]

Dan Sommers wrote:

Let the DB-API do more work for you:

cursor = connection.cursor( )
sql = """SELECT column2, columns3 FROM table WHERE name LIKE %s"""
values = ('%%%s%%' % searchterm,) # note that this is a tuple

It looks like this might be a rare case where not using the % operator
might make it easier to see what's going on:

values = ('%' + searchterm + '%',)

-Peter