In SQL Server Management Studio (SSMS), when filtering events in XEvent Profiler, what is the difference between the Contains operator and the Like operator? I haven't been able to find any documentation on this.
Here is the Filters dialog for the XEvent Profiler, showing the list of possible comparison operators, including Contains
and Like
:
I couldn't find this documented.
I expected that LIKE
would behave as the TSQL LIKE
keyword and support %
as a wildcard. In reality this does not work.
From looking at the decompiled FilterBinaryExpression
class in Microsoft.SqlServer.XEventStorage.dll
it looks like it produces a Regex by processing the string as follows (so supports *
and ?
as wildcards for arbitrary length strings and single characters respectively - analogous to %
and _
in the SQL Like syntax)
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("^");
foreach (char ch in wildcard)
{
switch (ch)
{
case '*':
stringBuilder.Append(".*");
break;
case '?':
stringBuilder.Append(".");
break;
default:
stringBuilder.Append(Regex.Escape(ch.ToString()));
break;
}
}
stringBuilder.Append("$");
this.wildcardRegex = new Regex(stringBuilder.ToString(), RegexOptions.IgnoreCase | RegexOptions.Singleline);
The usage of this regex then checks
wildcardRegex.IsMatch(str)
Whereas the Contains filter just checks
str.IndexOf(this.value, StringComparison.CurrentCultureIgnoreCase) != -1
So does a case insensitive check if the string contains the literal substring.