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