How to escape underscore character in PATINDEX pattern argument?

sql-server regex tsql escaping sql-like
podosta picture podosta · May 14, 2009 · Viewed 54.1k times · Source

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?

Answer

Curt Hagenlocher picture Curt Hagenlocher · May 14, 2009

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

Related questions

Using RegEx in SQL Server

I'm looking how to replace/encode text using RegEx based on RegEx settings/params below: RegEx.IgnoreCase = True RegEx.Global = True RegEx.Pattern = "[^a-z\d\s.]+" I have seen some examples on RegEx, but confused as to how to apply …

Read More
SQL Server Regular expressions in T-SQL

Is there any regular expression library written in T-SQL (no CLR, no extended SP, pure T-SQL) for SQL Server, and that should work with shared hosting? Edit: Thanks, I know about PATINDEX, LIKE, xp_ sps and CLR solutions I also …

Read More
How to use regular expression in TSQL?

I'd need to have a regular expression so that I can find all invalid email addresses in our database table. SELECT * FROM dbo.[Users] AS u WHERE u.EmailAddress not like 'regular expression of valid email addresses' Does SQL Server …

Read More