HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

Use square brackets on extended events filter

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
eventsextendedbracketsfiltersquareuse

Problem

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

Solution

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

Code Snippets

WHERE 
    [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text], N'%[[]demo]%')
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')

Context

StackExchange Database Administrators Q#316116, answer score: 8

Revisions (0)

No revisions yet.