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.
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: