Use square brackets on extended events filter

Akhter Khan 1 Reputation point

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:

ADD EVENT sqlserver.sql_batch_completed(
WHERE ([sqlserver].like_i\_sql_unicode_string)
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 on the filter and then:

n.ev.value('@timestamp','datetimeoffset') as [timestamp],
n.ev.value('data@DeezNutz ="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 = N'demo' AND xet.target_name = N'ring_buffer'
and n.ev.value('data@DeezNutz ="batch_text"','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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,583 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Viorel 111.5K Reputation points

    It seems that you did not try like '%[[]demo]%' yet.

    Also check this: like '%\[demo\]%' escape '\'.

  2. YufeiShao-msft 7,051 Reputation points

    Hi @Akhter Khan

    On the SQL Server, square brackets have a special meaning when used in a query filter, in order to match a square bracket notation must be used with a left bracket in the character set, as in "[[]", and the right square bracket does not need any special handling


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.