Use square brackets on extended events filter

Question

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:

-- m

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"][1]','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"][1]','varchar(max)') like '%\[demo\]%' escape '\'

But this still captures more events than necessary.

I've tried '%[[]demo[]]%', '%{[}demo{]}%', '%\[demo\]%', '%$[demo$]%', and none of those worked.

Answer 1

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.

Symbol	Meaning
LIKE '5[%]'	5%
LIKE '[_]n'	_n
LIKE '[a-cdf]'	a, b, c, d, or f
LIKE '[-acdf]'	-, a, c, d, or f
LIKE '[ [ ]'	[
LIKE ']'	]
LIKE 'abc[_]d%'	abc_d and abc_de
LIKE 'abc[def]'	abcd, abce, and abcf

Test

SELECT ('a_demo_a')
GO
SELECT ('a_[demo_a')
GO
SELECT ('a_demo]_a')
GO
SELECT ('a_[demo]_a')
GO
SELECT ('m')
GO
SELECT ('a_[[demo]_a')

Result