topic Re: quotes and double quotes in an SQL expression in Python Questions

quotes and double quotes in an SQL expression

ZdeněkSoldán — Mon, 01 Feb 2016 14:23:22 GMT

Hello

I have a layer with a string attribute Layer and I want to select every record that contains string PLOCH anywhere in the whole string and doesn't end on character _ using Python script.

My expression is sqlprikaz='"Layer" LIKE ' + "'%PLOCH%'" and "Layer" <> "%_"

First I had only the first part of the expression and it worked but when I add the second part I have a problem where quotes and double quotes should to be and the expression doesn't work.

Can anyone explain me the rules of quotes and double quotes in expressions with more conditions please?

Re: quotes and double quotes in an SQL expression

JakeSkinner — Mon, 01 Feb 2016 14:59:39 GMT

Hi Zdenek,

I believe your expression should be the following:

sqlprikaz = "Layer LIKE '%PLOCH%' AND Layer <> '%_'"

The entire expression will be in double quotes, and each string you are searching for (or not search for) will be in single quotes.

Re: quotes and double quotes in an SQL expression

BenLeslie1 — Mon, 01 Feb 2016 15:27:51 GMT

I think you also need to use NOT LIKE instead of <> so the percentage sign works in the second part.

sqlprikaz = "Layer LIKE '%PLOCH%' AND Layer NOT LIKE '%_'"

Re: quotes and double quotes in an SQL expression

PeterWilson — Mon, 01 Feb 2016 19:48:32 GMT

Hi Zdenek,

I propose that you use the following:

sqlprikaz = "Layer LIKE '%PLOCH%' AND NAME NOT LIKE '%_'"

I recommend you have a look at AddFieldDelimiters and Regular Expressions.

Regards

Re: quotes and double quotes in an SQL expression

KimOllivier — Sat, 11 Dec 2021 07:55:48 GMT

You could use the modern format() unction for strings to more easily assemble SQL strings from variables.

This is easy to then just use single quotes around SQL string expressions.

If you want to enclose sql variable names in double quotes, use treble quotes to enclose the expression.

You don't need to use double quotes if the variable names are valid names, no spaces and not reserved words and are not using Microsoft Access. Who uses personal geodatabases any more? I don't bother with the field delimeter function personally because I know my target database is not Access.

If you always use treble quotes it is nicely highlighted in my IDE (pyscripter) differently from other strings.

Note that the format statement allows multiple references to one variable, reducing duplication.

It is also easier to strip unicode prefixes from strings in the function using a encode function if necessary.

The format expression is much easier to read because it is closer to the literal string and is easier to debug by removing most of the quote characters.

sqlprikaz='"Layer" LIKE ' + "'%PLOCH%'" and "Layer" <> "%_"

layer = 'Layer_name'
search_string = 'PLOCH'
sqlprikaz = """{0} LIKE '%{1}%' AND {0} NOT LIKE '%_'""".format(layer,search_string)
print sqlprikaz