topic Re: ArcGIS 10.2 - SQL syntax with quote in Python to build a layer? in Python Questions

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

VincentLaunstorfer — Mon, 02 Feb 2015 15:35:11 GMT

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

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

ErinBrimhall — Mon, 02 Feb 2015 16:32:25 GMT

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 + "`"

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

VincentLaunstorfer — Mon, 02 Feb 2015 17:19:29 GMT

Thanks but unfortunately, it does'nt work!

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

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

XanderBakker — Fri, 10 Dec 2021 21:15:03 GMT

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'

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

JoshuaBixby — Mon, 02 Feb 2015 17:43:24 GMT

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. : )

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

XanderBakker — Mon, 02 Feb 2015 17:45:48 GMT

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

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

VincentLaunstorfer — Tue, 03 Feb 2015 13:50:30 GMT

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.