T-SQL Regular Expression select email from string

I have column in a table containing string like this- "Executive writer's email is john.dio@pluto.com , ref @4567..."

All I need is to get the email from the string. I have been doing this with ease using the great software called editpad pro but now I am dealing with around 10 million records and no editor supports this k开发者_如何学编程ind of volume.

How can I write T-SQL in 2008 to select regex matching value? The regex to find valid email is "\b[A-Z0-9._%+-]*@[A-Z0-9.-]+.[A-Z]{2,4}\b"

Many thanks.

You can use grep to extract all matches from a huge amount of text:

grep <regexp> <filename>

An easy way to get grep running on Windows is to download the MingW32 version of Git.

SQL Server doesn't natively support regular expressions - you have to use CLR functionality to extend SQL Server 2005+ functionality for things like regex support. This link provides assemblies that can be deployed, but most shops don't allow CLR.

CHARINDEX might help, but PATINDEX has limited pattern matching support.

If you need to get a value out, you should really be storing it in it's own column rather than somewhere in text.

i think you could probably find significantly more efficient ways of doing this than tsql, but if that's the tool you can use, here's an article with some functions that create regular expression support using just tsql functions and ole automation.