By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,813 Members | 1,130 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,813 IT Pros & Developers. It's quick & easy.

string formatting using the % operator

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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
Jul 19 '05 #2

P: n/a
> 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.
--

Jul 19 '05 #3

P: n/a
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/>
Jul 19 '05 #4

P: n/a
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!
Jul 19 '05 #5

P: n/a
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
Jul 19 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.