December 10, 2010

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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

0 Responses to "Does SQL Server support Regular Expressions"
 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions