Does SQL Server support Regular Expressions

I saw an interesting discussion between devs, the other day on twitter. The topic was ‘Are Regular Expressions supported in SQL Server?’

The answer is yes and no. SQL Server does not support Regular expressions directly. So when you are using T-SQL, the support for Regular Expression ‘Syntax’ in SQL Server is very limited. For example, you can do some pattern matching using some expression operators, with the LIKE or PATINDEX operators. Here are some examples:

Character

Description

Usage

%

Matches a string of zero or more characters. Eg: Match the word 'Curry' anywhere in the Name column

SELECT … FROM TABLE WHERE Name LIKE '%Curry%'

Underscore (_)

Matches a single characters (newslines included) Eg: Match all five letter names that ends with ‘urry’

SELECT … FROM TABLE WHERE Name LIKE '_urry'

[ …]

Matches any character within a specific set Eg: Match names which end with ‘urry’ and start with any single character between A and D – like Aurry, Burry, Curry

SELECT … FROM TABLE WHERE Name LIKE '[A-D]urry'

[^…]

Matches any character not within a specific set. Eg: Match names which end with ‘Curr’ and following letter is not between M and T.

SELECT … FROM TABLE WHERE Name LIKE 'Curr[^M-T]'

Since Regular expressions were not supported directly in SQL Server, developers working on SQL Server 2000 have used less efficient techniques like using a UDF with VBScript.RegExp library or use extended stored procedures.

However with the SQL Server 2005/2008 support for hosting the common language runtime (CLR), it is now possible to write CLR regular expressions. There is an excellent article that shows how to do it and I strongly recommend you to read it

Regular Expressions Make Pattern Matching And Data Extraction Easier


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

2 comments:

Unknown said...

The link for "Regular Expressions Make Pattern Matching And Data Extraction Easier" (msdn.microsoft.com/en-us/magazine/cc163473.aspx) no longer exists. I wish the author would put the information into the article.

Suprotim Agarwal said...

@Unknown - I am sorry but I can't find the updated link as Microsoft removed it. So here's something from the archives http://web.archive.org/web/20081207115019/http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

HTH