SQL Server Regular expressions in T-SQL

sql-server regex tsql
xnagyg picture xnagyg · Oct 11, 2008 · Viewed 160.6k times · Source

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 know it is not the best place for regex, the question is theoretical :)
  • Reduced functionality is also accepted

Answer

Eric Z Beard picture Eric Z Beard · Oct 13, 2008

How about the PATINDEX function?

The pattern matching in TSQL is not a complete regex library, but it gives you the basics.

(From Books Online)

Wildcard  Meaning  
% Any string of zero or more characters.

_ Any single character.

[ ] Any single character within the specified range 
    (for example, [a-f]) or set (for example, [abcdef]).

[^] Any single character not within the specified range 
    (for example, [^a - f]) or set (for example, [^abcdef]).

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
How to escape underscore character in PATINDEX pattern argument?

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 (…

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