How do I select a regex match from a text/varchar in MS SQL?

Question

I need to extract something from a long piece of text across lots of db rows, in a Microsoft SQL Server database.

I could write a script to loop through and extract it, but I was hoping there was nice simple way I can do some SQL like:

SELECT IpAddress = matchFirst('RegEx',ColName)
FROM table
WHERE conditions

I've looked about but all I'm finding is unclear long-winded ramblings about using regex in the where clause and CLR UDFs and stuff - but all I want is a simple "insert regex here" answer.

Anyone ideas?

Answer 1

There isn't support for regular expressions built in to TSQL, so unfortunately there isn't an immediate way to just do it.

So you could either:
1) write a UDF function that performs string manipulation to try to parse the IPAddress out of a supplied string (i.e. using the functionality TSQL does support like PATINDEX, SUBSTRING etc) - if you didn't want to use SQLCLR for whatever reason

2) use SQL CLR to allow you to run regular expressions in a query - would need a .NET assembly to do the reg ex stuff, that can then be hooked into and called directly from your SQL query. The code to do this is already out there in a number of places. Apologies if this is one place you already looked, but have a look at this MSDN blog post - that gives the .NET code for the regex stuff, and an example of how to use it. And also this MSDN article on How to create and run a CLR SQL Server User-Defined Function

Answer 2

If you're looking for a simple solution I would suggest using the SQL# library which basically contains the UDF you need and you'll find referenced elsewhere.

Once that's installed (it's reasonably painless to install) you will find a function called RegEx_MatchSimple which I believe is what you need.

Answer 3

i know sql has at least some slight reg-ex compatibility, since you can do the following (sql 2005, 2008)

select * from table where CharField like '[a-z]%'

perhaps if you tell exactly what you need your regex to do someone might be able to give you a sql equivilent.