AnsweredAssumed Answered

SQL in Python where statement using LIKE

Question asked by yeel on Jul 16, 2013
Latest reply on Jul 31, 2013 by JimGirvan
Hi all, I'd like to construct a SQL statement for a search cursor using a LIKE with a wildcard instead of an =...but I'm having some difficulty as I find SQL in python with all it's backslashes, apostrophes, double quotes and escapes to be rather confusing. I don't know where to put the wildcard "%" and how to escape it in the code...

Here is what I've tried (couldn't get the script to work with "LIKE" but since I'm trying to find road names "LIKE" is necessary.

import arcpy import os import re  basedir = r"C:\Test" fc = r"G:\GIS\ParcelsPublic.shp" field1 = "ADD1" field2 = "STREETNAME"  try:     for fn in os.listdir(basedir):         add = re.sub(r"\D"," ", fn)         add = str.lstrip(add)         add = str.upper(add)         street = re.sub(r"[^a-zA-Z]"," ",fn)         street = str.lstrip(street)         street = str.upper(street)         print (add + " " + street)         expression = '"' + field1 + '"= ' + "'%s'" %add         expression2 = '"' + field2 +'"= ' + "'%s'" %street         expression3 = expression + " "+ "AND" +" " + expression2         rows = arcpy.SearchCursor(fc, fields="STREETNAME; ARN; ADD1", where_clause=expression3)

And this was provided to me on stackexchange but I haven't been able to implement a wildcard:
expression3 = '"{field1}" = \'{add}\' AND "{field2}" LIKE \'{street}\''.format(field1=field1, add=add, field2=field2, street=street)

Outcomes