quotes and double quotes in an SQL expression

[ZdeněkSoldán]

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?

[JakeSkinner]

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.

[BenLeslie1 · Accepted solution]

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 '%_'" 

[PeterWilson]

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

[KimOllivier]

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