How to escape underscore character in PATINDEX pattern argument?
56,118
Solution 1
I've always done it with brackets: '%[_]%'
Solution 2
To match two underscores, each must be bracketed
'%[__]%' -- matches single _ with anything after
'%[_][_]%' -- matches two consecutive _
Solution 3
You can escape using the [
and ]
characters like so:
PRINT PATINDEX('%[_]%', '37_21')
Author by
podosta
Updated on July 05, 2022Comments
-
podosta almost 2 years
I've found a solution for finding the position of an underscore with PATINDEX :
DECLARE @a VARCHAR(10) SET @a = '37_21' PRINT PATINDEX('%_%', @a) -- return 1 (false) PRINT PATINDEX('%!%', REPLACE(@a, '_', '!')) -- return 3 (correct)
Have you other ideas? Like a way to escape the underscore character?