ArcGIS 10.2 - SQL syntax with quote in Python to build a layer?

[VincentLaunstorfer]

Hi,

I have a python tool in which an SQL query string make a layer:

"NAME " + "LIKE " + "'" + strField_NAME + "'"

However, it appears that I have some names containing quotes in my dataset! For example, Jimmy's Peak containing a single quote and thus crashing my script...

Is there any sort of escape character I could use in a Python script in order to build an SQL query?

For the time being, the query builds as NAME LIKE + 'Jimmy' because the name contains a single quote... and it should be NAME LIKE + Jimmy's Peak

[ErinBrimhall]

I believe you can use a backtick (grave accent) instead of a single quotation mark to wrap the text value in your query.  So, your python tool SQL query string would become:

"NAME " + "LIKE " + "`" + strField_NAME + "`"

[VincentLaunstorfer]

Thanks but unfortunately, it does'nt work!

It looks silly but I don't know how to select text value which include quotation marks...

[XanderBakker · Accepted solution]

If I manually perform this query in a fgdb featureclass, then it creates a SQL like this:

NAME LIKE 'Jimmy''s Peak'

So I assume in Python you could do something like:

name = "Jimmy's Peak"
sql = "NAME LIKE '{0}'".format(name.replace("'", "''"))
print sql

# returns:
>> NAME LIKE 'Jimmy''s Peak'

[VincentLaunstorfer]

Thanks, it did help but I could not make the syntax work, so I wrote:

"NAME " + "LIKE " + "'" +strField_NAME.replace("'", "''") + "'"

...but I could have replaced the single quote with anything because this SQL is just to make a feature layer.

Also, I could use the same technique with any other character Python and/or SQL would not like.

[JoshuaBixby]

It is helpful to review the Help for Building a query expression, where it states:

If the string contains a single quote you will first need to use another single quote as an escape character.

In Python, the backslash is the dominant escape character, but there are some additional escape sequences. ArcGIS wants users to escape a single quote by using another single quote.

I see that Xander Bakker‌ beat me to the punch with the actual code.  That's what getting distracted while replying gets me. : )

[XanderBakker]

The least I can do is mark your answer as helpful...