sql server - 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

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.