September 14, 2007 at 9:37 am
Greetings all...
I haven't put much thought to this yet but I thought I'd throw out this question. Has anyone developed a email string search function where by you can identify a valid email address in the middle of a string?
I need to scrub several million rows of data replacing email address with some text, either blank or or something like that.
the simplest way is to first see if the string has an @ located in it then I would need to see if it has been embedded in a valid email address....
I welcome your thoughts...
Kurt
DBA
RHWI
Poughkeepsie, NY
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
September 14, 2007 at 10:22 am
If I only needed to do it once, I'd probably extract it and use perl because it's much better at regular expressions. I was able to come up with this... it's ugly, but it seems to work.
DECLARE@varNVARCHAR ( 200 )
SET @var = 'text some thing myemail@email.com something else'
SELECT LEFT(@var,
CHARINDEX('@', @var) -- First location of @
- CHARINDEX(' ', REVERSE(LEFT(@var, CHARINDEX('@', @var)))))-- First space to the left of @
+ ' [replacement text] ' +
RIGHT(@var, LEN(@var) - CHARINDEX(' ', @var, CHARINDEX('@', @var)))
Though your performance for all those string functions will probably suck and it won't remove multiple e-mail addresses, nor does it validate that it's an actual e-mail address. I'm sure there's got to be a good article on a better way to do it.
September 14, 2007 at 10:44 am
Thanks Aaron... I'll give it a try..
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
September 14, 2007 at 1:13 pm
Aaron...
This works great but it has issues. If there were more than 1 email address in the text then it would only find the first one. So it is going to have to be recursive which doesn't excite me at all because it may have to process each row through a cursor....
Still scratching my head...
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
September 14, 2007 at 1:19 pm
It's a shame you're running this in 2000 - you could have thrown a regular express at it in 2K5....
Of course - there's some stuff over here that might help with that... It involves creating your own extended stored proc, but if it works....
http://www.codeproject.com/managedcpp/xpregex.asp
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply