select email addresses from html source

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

select email addresses from html source

Author

Topic

whitlander
Starting Member

2 Posts

Posted - 2014-01-28 : 12:55:53

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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-01-28 : 15:07:38

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

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-01-28 : 19:31:45

[CODE]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[/CODE]

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

whitlander
Starting Member

2 Posts

Posted - 2014-01-29 : 06:05:45

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

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-01-29 : 13:00:40

Small change. It occurred to me that the before and after can be found at the same time (Duh!):[CODE]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[/CODE]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources