topic Re: Expression substitution using wildcards and variables in Geoprocessing Questions

Expression substitution using wildcards and variables

AlexanderDunkel — Wed, 07 Sep 2011 18:27:28 GMT

I have a problem I can not somehow solve on my own: I am trying to make a Select Query in ModelBuilder, using a substitution of a User-Input String and an Expression with Wildcards. The problem is, that I need to use the %-Wildcard, and thats the same as for the string-substitution.
This one works:
"Tags" LIKE '%music%' whereas this one works not (with the input-string):
"Tags" LIKE '%%string%%'

I tried all sorts of combinations, like
"Tags" LIKE '%'%string%'%'
"""Tags" LIKE '%"""%string%"""%'"""

but it is giving me errors all the time. Do you know the correct format of this query?

Re: Expression substitution using wildcards and variables

ShitijMehta — Wed, 07 Sep 2011 20:47:06 GMT

Can you add a snapshot of your model and the select query dialog?
How/Why/Where are you using the wild card substitution?

These wont work:
"Tags" LIKE '%%string%%'
"Tags" LIKE '%'%string%'%'
"""Tags" LIKE '%"""%string%"""%'"""

Re: Expression substitution using wildcards and variables

AlexanderDunkel — Wed, 07 Sep 2011 21:07:47 GMT

Hello shitijmehta,

I attached 4 images:
modelb1.jpg shows the Modelview with the Select-task (where I am having the problem) and modelb2.jpg the Select-task configuration. In this case, I am getting the Expression from outside (modelb3.jpg). But instead of putting the final text in the Expression ("Tags" LIKE '%music%') I want to use the String-Parameter (modelb4.jpg) and use that one instead of the part "music" in the Expression. So you can enter the search-string while opening the programm. I don't know how to substitute the expression with the wildcards % and the String-Variable (which would also need % before/after).
Does this make sense to you?

Re: Expression substitution using wildcards and variables

ShitijMehta — Wed, 07 Sep 2011 21:41:54 GMT

Try:
In the expression variable

"Tags" LIKE '%String%'

This is enough as at the run time the %string% will be replace by the value of the string i.e. music

This is what you are doing:
You are creating a variable for a wild card that you want to use. This variable is of data type string.
The name of the variable is String (rename it to something else- xyz and use- "Tags" LIKE '%xyx%')
You are substituting the variable name %String%/%xyz% in the expression

Re: Expression substitution using wildcards and variables

AlexanderDunkel — Wed, 07 Sep 2011 21:53:09 GMT

Try:
In the expression variable

"Tags" LIKE '%String%'

This is enough as at the run time the %string% will be replace by the value of the string i.e. music

This is what you are doing:
You are creating a variable for a wild card that you want to use. This variable is of data type string.
The name of the variable is String (rename it to something else- xyz and use- "Tags" LIKE '%xyx%')
You are substituting the variable name %String%/%xyz% in the expression

Unfortunately, this isn't working - I tried it before. When I do it like this:
"Tags" LIKE '%String%'
where the variable string = "music" it wont select any feature, but with this:
"Tags" LIKE '%music%'
it will select more than 100 features. From my understanding - I am not really an expert, so please ignore if I am saying something wrong - for inline-substitution of String-Variables you need the Variable name, enclosed by %%. For my Select-Expression, I also need enclosing %% before and after the search-tearm as wildcards. I want to select all Features, that have music in the Tag-Coulm (even if its like abcdmusicdefg etc.). Therefore, I need the %-Wildcards. Logically, my Expressio should look like this:
"Tags" LIKE '%%String%%'

The inner %% for the Variable-String I am getting as a Parameter from the User and the outer %% as Wildcards in the expression. But this gives me an error.

Re: Expression substitution using wildcards and variables

AlexanderDunkel — Wed, 07 Sep 2011 22:27:47 GMT

I think the result window shows it more clearly whats going on:

With
"Tags" LIKE '%String%'
(String = "music") it is doing this:
"Start Time: Wed Sep 07 16:24:20 2011
Executing (Select): Select Locations\xxx C:\xxx ""Tags" LIKE 'music'"

instead of ""Tags" LIKE '%music%'".
Now, when I enter myself %music% as the Parameter for String, it is doing this - the correct query:
"Start Time: Wed Sep 07 16:24:45 2011
Executing (Select): Select Locations\xxx C:\xxx ""Tags" LIKE '%music%'"

[edit]
Great - finally found a solution, although it looks a little bit strange:

I have now 2 Variables
Tagname = "music"
and
Perc I'll define as "%"

looks like this in the Expression of the Select-Query:
"Tags" LIKE '%Perc%%Tagname%%Perc%'

and is doing this after run:

Start Time: Wed Sep 07 16:49:39 2011
Executing (Select): Select Locations\xxx C:\xxx ""Tags" LIKE '%music%'"

Re: Expression substitution using wildcards and variables

ShitijMehta — Thu, 08 Sep 2011 16:05:28 GMT

I am glad you found a workaround.

This is what we found from our testing:

"%string%" - Works
"%string%%" - Works
"%%string%%" - Unfortunately fails. We will work on fixing it.

Meanwhile there is another workaround.
See attached image and the toolbox (version 10).

Re: Expression substitution using wildcards and variables

AlexanderDunkel — Thu, 08 Sep 2011 16:19:33 GMT

Great! Your solution looks cleaner - I was trying around with "+" Phyton String Substitution, but couldn't get it to work. This is a nice example. Thank you for your reply.