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

4450
6
Jump to solution
02-02-2015 07:35 AM
VincentLaunstorfer
Occasional Contributor III

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

0 Kudos
1 Solution

Accepted Solutions
XanderBakker
Esri Esteemed Contributor

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'

View solution in original post

6 Replies
ErinBrimhall
Occasional Contributor II

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

0 Kudos
VincentLaunstorfer
Occasional Contributor III

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
Esri Esteemed Contributor

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
Occasional Contributor III

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.

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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
Esri Esteemed Contributor

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

0 Kudos