CODEWITHSUNDEEP
c#sqlsql-serverregex
Resorath
Resorath

Reputation: 1702

Matching a string against a column full of regular expressions

I have a column in a table that is full of regular expressions.

I have a string in my code, and I want to find out which regular expressions in that column would match that string and return those rows.

Aside from pulling each row and matching the regular expression (which is costly, checking against potentially thousands of records for a single page load) is there a way I can do this in SQL instead with one (or a couple) queries?

Example input: W12ABC

Example column data

1   ^[W11][\w]+$  
2   ^[W12][\w]+$  
3   ^[W13][\w]+$ 
4   ^[W1][\w]+[A][\w]+$  
5   ^[W1][\w]+[B][\w]+$  
6   ^[W1][\w]+[C][\w]+$

Should return rows 2 and 4.

Upvotes: 5

Views: 381

Answers (2)

Ted Elliott
Ted Elliott

Reputation: 3493

If you're open to wildcards you can store patterns that work for LIKE syntax: "W12%" or "W%ABC". Your query would look like this:

SELECT * FROM Table Where 'W12ABC' LIKE Column

Upvotes: 1

Chris Dickson
Chris Dickson

Reputation: 12135

T-SQL doesn't provide this functionality, but you can enable it in SQL Server by writing a CLR function using the .NET regular expression classes. There is a good article here explaining how to do it.

Upvotes: 1

Related Questions