How to escape underscore character in PATINDEX pattern argument?

Question

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?

score 124 · Accepted Answer · edited Dec 16 '14 at 22:41

124

I've always done it with brackets: '%[_]%'

edited Dec 16 '14 at 22:41

abatishchev

98,240
88
296
433

answered May 14 '09 at 14:14

Curt Hagenlocher

20,680
8
60
50

8

+1 see: http://msdn.microsoft.com/en-us/library/ms187489(SQL.90).aspx, Section "Searching for Wildcard Characters" – Tomalak May 14 '09 at 14:17

Leif Neland · Answer 2 · 2018-05-07T23:11:42.440

27

To match two underscores, each must be bracketed

'%[__]%' -- matches single _ with anything after

'%[_][_]%' -- matches two consecutive _

edited May 07 '18 at 23:11

answered Dec 18 '12 at 15:14

Leif Neland

1,416
1
17
40

score 6 · Answer 3 · edited Oct 26 '12 at 20:59

6

You can escape using the [ and ] characters like so:

PRINT PATINDEX('%[_]%', '37_21')

edited Oct 26 '12 at 20:59

Ryan Gates

4,501
6
50
90

answered Apr 11 '12 at 19:11

Charlie

61
1
1

How to escape underscore character in PATINDEX pattern argument?

3 Answers3

Linked