I want create an extended events session and use the
like_i_sql_unicode_string operator to filter the phrase
[demo], with the square brackets.
I've started with:
CREATE EVENT SESSION [demo] ON SERVER ADD EVENT sqlserver.sql_batch_completed( WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%[demo]%')) ) ADD TARGET package0.ring_buffer alter event session [demo] on server state=start
But this interprets
[demo] as a character group on a regex-like syntax. So if I run this:
It will be captured on the extended event.
The closest I've got was filtering it later, using
[sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%demo%') on the filter and then:
SELECT n.ev.value('@timestamp','datetimeoffset') as [timestamp], n.ev.value('data[@name="batch_text"]','varchar(max)') as [batch_text] FROM sys.dm_xe_session_targets xet INNER JOIN sys.dm_xe_sessions xe ON xe.[address] = xet.event_session_address cross apply (select CONVERT(XML, target_data) as xData ) as x cross apply x.xData.nodes(N'RingBufferTarget/event') AS n(ev) WHERE xe.name = N'demo' AND xet.target_name = N'ring_buffer' and n.ev.value('data[@name="batch_text"]','varchar(max)') like '%\[demo\]%' escape '\'
But this still captures more events than necessary.
'%$[demo$]%', and none of those worked.
This was challenging.
WHERE [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text], N'%[demo]%')
When testing it, even the opening bracket is regarded as a [ ] (Wildcard - Character(s) to Match
So the first part of the condition has the opening bracket nested.
The closing bracket alone is not recognized as a special character, so no extra care is needed.
Together both these conditions surround the searched text.
This is documented under Using Wildcard Characters As Literals in the documentation for LIKE (Transact-SQL) (emphasis added):
You can use the wildcard pattern matching characters as literal characters. To use a wildcard character as a literal character, enclose the wildcard character in brackets. The following table shows several examples of using the LIKE keyword and the [ ] wildcard characters.
External links referenced by this document: