SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 select email addresses from html source
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

whitlander
Starting Member

2 Posts

Posted - 01/28/2014 :  12:55:53  Show Profile  Reply with Quote
I have dumped html in a table and am looking to extract email addresses from it. Within the html, the email addresses are followed and preceded by blank spaces. I am playing around with the following code:

SELECT RIGHT(@email, LEN(@email) - CHARINDEX('@', @email)) as Domain

...but have been unable to extract whole email addresses. Can you help?

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 01/28/2014 :  15:07:38  Show Profile  Reply with Quote
>> Can you help?
probably - but you didn't provide much to go on. Post some sample values and post what you want to extract from those values. (in other words: sample input and expected output)

Be One with the Optimizer
TG
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1677 Posts

Posted - 01/28/2014 :  19:31:45  Show Profile  Reply with Quote
declare
   @string   varchar(1000) = 'Intro stuff xyz@abc.org trailing stuff;';

;with WhereYouAt
as (
select
   @string strg,
   charindex('@', @string) as AtIdx
),
SpaceAfterAt
as (
select
   a.strg,
   a.AtIdx,
   charindex(' ', a.strg, a.AtIdx+1) AfterIdx
from
   WhereYouAt a
),
SpaceBeforeAt
as (
select
   a.strg,
   a.AtIdx,
   a.AfterIdx,
   charindex(' ', reverse(substring(@string, 1, a.AtIdx - 1))) BeforeIdx
from
   SpaceAfterAt a
)
select
   substring(b.strg, b.AtIdx - b.BeforeIdx + 1, b.AfterIdx - (b.AtIdx - b.BeforeIdx) - 1)
from
   SpaceBeforeAt b


===============================================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber (1894-1961)
Go to Top of Page

whitlander
Starting Member

2 Posts

Posted - 01/29/2014 :  06:05:45  Show Profile  Reply with Quote
Thank you for the replies. I'll try your code later today Bustaz, thank you. And yes I should have been more specific. I have a field into which I have dumped html and stripped out all the tags leaving e.g

'For further information please contact this@emailaddress.co.uk'

...and so I'm looking to extract 'this@emailaddress.co.uk' only.

Thanks
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1677 Posts

Posted - 01/29/2014 :  13:00:40  Show Profile  Reply with Quote
Small change. It occurred to me that the before and after can be found at the same time (Duh!):
declare
   @string   varchar(1000) = 'Intro stuff xyz@abc.org trailing stuff;';

;with WhereYouAt
as (
select
   @string strg,
   charindex('@', @string) as AtIdx
),
SpaceBeforeAfterAt
as (
select
   a.strg,
   a.AtIdx,
   case    -- Handle when there is no trailing space
      when charindex(' ', a.strg, a.AtIdx+1) = 0 then len(a.strg) + 1
      else charindex(' ', a.strg, a.AtIdx+1)
      end AfterIdx,
   case    -- Handle when there is no leading space
      when charindex(' ', reverse(substring(a.strg, 1, a.AtIdx - 1))) = 0 then a.AtIdx
      else charindex(' ', reverse(substring(a.strg, 1, a.AtIdx - 1)))
      end BeforeIdx
from
   WhereYouAt a
)
select
   substring(b.strg, b.AtIdx - b.BeforeIdx + 1, b.AfterIdx - (b.AtIdx - b.BeforeIdx) - 1)
from
   SpaceBeforeAfterAt b


===============================================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber (1894-1961)

Edited by - Bustaz Kool on 01/29/2014 18:24:30
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000