snippetsqlMinor
Use square brackets on extended events filter
Viewed 0 times
eventsextendedbracketsfiltersquareuse
Problem
I want create an extended events session and use the
I've started with:
But this interprets
It will be captured on the extended event.
The closest I've got was filtering it later, using
But this still captures more events than necessary.
I've tried
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=startBut this interprets
[demo] as a character group on a regex-like syntax. So if I run this:-- mIt 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.
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
Result
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.