CODEWITHSUNDEEP
regext-sql
user219628
user219628

Reputation: 3905

T-SQL Regular Expression select email from string

I have column in a table containing string like this- "Executive writer's email is [email protected] , 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 kind 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.

Upvotes: 0

Views: 1951

Answers (3)

nathan gonzalez
nathan gonzalez

Reputation: 11987

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.

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332571

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.

Upvotes: 1

Andomar
Andomar

Reputation: 238086

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.

Upvotes: 3

Related Questions