If/Then Statement with LIKE using the Field Calculator

Question

I cannot get the below to work with a LIKE variable within the If/Then statement. Is there something that I am missing?

Prelogic Script

dim y

If [Categories] LIKE '%Orange%' Then
y = "Medium"
Else y = [Categories] 
End If

Status = 
y

Sorry I didn't format it correctly previously. I did have it formatted this way and it says, "There was a failure during processing, Check geoprocessing window for details" — John D
– John D, Commented Jul 19, 2018 at 20:06
Your expression is in VisualBasic (VB), but there is no like operator in VB. Like comes from SQL. You want to look for string functions in VB or switch to Python, where you have even more possibilities. — Andreas Müller
– Andreas Müller, Commented Jul 19, 2018 at 20:54
May be i was wrong, try to use * instead of % (docs.microsoft.com/de-de/dotnet/visual-basic/language-reference/…) — Andreas Müller
– Andreas Müller, Commented Jul 19, 2018 at 21:11
@AndreasMüller is right, % is SQL operator for LIKE clause. However, I would use python for the same calculation in one line, e.g., "Medium" if "Orange" in !Categories! else !Categories! — fatih_dur
– fatih_dur, Commented Jul 20, 2018 at 1:16

Carlos MSF · Accepted Answer · 2018-08-07 09:28:45Z

There is no LIKE comparison operator in VB Script (see link).

You could use InStr to check if some search text appears somewhere in the input string:

dim y

If InStr([Categories], "Orange") Then
  y = "Medium"
Else
  y = [Categories] 
End If

Status = y

InStr will return 0 if the search text is not found, which will be interpreted as False in the condition statement.

If you want to search some text explicitly in the begining of the input string, just check if the value returned from InStr is 1.

If you want to search some text in the end of the input string, try Right function to extract part of the string and then do the comparison.

1 Answer 1