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]

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?

'%here you go%'

Cheers,

- harold -

 

[Dan Sommers]

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

 

[William Gill]

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]

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