Expression substitution using wildcards and variables

1254
7
09-07-2011 11:27 AM
AlexanderDunkel
New Contributor
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?
0 Kudos
7 Replies
ShitijMehta
Esri Regular Contributor
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%"""%'"""
0 Kudos
AlexanderDunkel
New Contributor
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?
0 Kudos
ShitijMehta
Esri Regular Contributor
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
0 Kudos
AlexanderDunkel
New Contributor
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.
0 Kudos
AlexanderDunkel
New Contributor
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%'"
0 Kudos
ShitijMehta
Esri Regular Contributor
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).
0 Kudos
AlexanderDunkel
New Contributor
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.
0 Kudos