topic Re: Concatenate string and variables in where clause? in Geoprocessing Questions

Concatenate string and variables in where clause?

ducksunlimited — Fri, 13 Aug 2010 18:11:03 GMT

Dear All,

Can anybody correct the syntax error in the following codes? I use personal GDB. I knew the probelm is with the where clause, but don't know how to fix it. thanks,

...
...
uid = row.GetValue('UID')
state = row.GetValue('STATE')

gp.Select_analysis(USA_State, USA_State_Select, "UID <> " + str(uid) AND "STATE = " + state)

...
...

Re: Concatenate string and variables in where clause?

ChrisMathers — Fri, 13 Aug 2010 18:32:44 GMT

You need to build the string outside of the tool perameters so that you can wrap parts of it in quotes for the tool. Remember this isnt just a string, its and SQL statement so you have to comply with SQL conventions.

>>>where="UID <> \'" +str(uid) + "\' AND STATE = \'" +state +"\'"
"UID <> 'oid' AND STATE = 'state'"

gp.Select_analysis(USA_State, USA_State_Select, where)

Re: Concatenate string and variables in where clause?

ducksunlimited — Fri, 13 Aug 2010 19:30:16 GMT

thanks for the reply

because i use mdb, i changed your code to where="[UID] <> \'" +str(uid) + "\' AND [STATE] = \'" +state +"\'"

and returned the error:

An invalid SQL statement was used. [SELECT * FROM USA_STATE WHERE [UID] <> '1' AND [STATE] = 'IL']

to me, this is a correct SQL expression. but it appreared as an error.

any help?

thanks

You need to build the string outside of the tool perameters so that you can wrap parts of it in quotes for the tool. Remember this isnt just a string, its and SQL statement so you have to comply with SQL conventions.

>>>where="UID <> \'" +str(uid) + "\' AND STATE = \'" +state +"\'"
"UID <> 'oid' AND STATE = 'state'"

gp.Select_analysis(USA_State, USA_State_Select, where)

Re: Concatenate string and variables in where clause?

RDHarles — Sat, 11 Dec 2021 16:30:32 GMT

Try this one (singles AND doubles around variables, brackets around PGDB's):

qry = "[UID] <> '"+uid+"' and [STATE] = '"+state+"'"
gp.Select_analysis(USA_State, USA_State_Select, qry)

Re: Concatenate string and variables in where clause?

BruceHarold — Mon, 16 Aug 2010 14:21:12 GMT

Hi

Don't forget that you can make your code workspace-portable by using the AddFieldDelimiters method to wrap field names, and Python's repr function to quote string literals in the SQL expression.

Something like:

uid = row.getValue('uid')
state = row.getValue('state')
ws = gp.Describe(USA_State).path
gp.Select_analysis(USA_State, USA_State_Select, gp.AddFieldDelimiters(ws,'UID') + " <> " + repr(uid) AND " + gp.AddFieldDelimiters(ws,'STATE') + " = " + repr(state))

Regards