How to escape underscore character in PATINDEX pattern argument?

Sql ServerRegexTsqlEscapingSql Like

Sql Server Problem Overview

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?

Sql Server Solutions

Solution 1 - Sql Server

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

Solution 2 - Sql Server

To match two underscores, each must be bracketed

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

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

Solution 3 - Sql Server

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

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

Categories
Recommended Sql Server Solutions
Recommended Regex Solutions
Recommended Tsql Solutions
Recommended Escaping Solutions
Recommended Sql Like Solutions

Previous Solution:

Is it possible to install a C# compiler without Visual Studio?

C#Compiler ConstructionMsbuildCsc

Next Solution:

Using BeautifulSoup to find a HTML tag that contains certain text

PythonRegexBeautifulsoupHtml Content-Extraction

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionpodostaView Question on Stackoverflow
Solution 1 - Sql ServerCurt HagenlocherView Answer on Stackoverflow
Solution 2 - Sql ServerLeif NelandView Answer on Stackoverflow
Solution 3 - Sql ServerCharlieView Answer on Stackoverflow