TSQL Challenge 41 - Extract email addresses from text data

Question

Post reply

TSQL Challenge 41 - Extract email addresses from text data

jacob sebastian

SSChampion

Points: 11822
More actions
October 16, 2010 at 4:42 pm

#141188

Comments posted to this topic are about the item TSQL Challenge 41 - Extract email addresses from text data
.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

crazy4sql SSCoach Points: 19590 More actions · Answer 1

-- I am assuming that format to extract mail id will be always like ' to mailid....'

--so scripted accordingly

declare @start2 int

declare @start3 varchar(100)

declare @start4 int

set @mail= 'I want write to abc.zyz@beyondrelational.com for getting my result'

print @mail

select @start = patindex('%@%',@mail)

select @start1 = patindex('%.com%',@mail)

select @start2 = patindex('%to%',@mail)

print @start

print @start1

print @start2

select @start3 = substring(@mail,@start2,(@start-@start2))+substring(@mail,@start,(@start1-@start))+substring(@mail,@start1,4)

print @start3

select substring(@start3,3,50)

--you can change value from 50 to any other int, in last select statement

----------
Ashish

Geoff A SSChampion Points: 11511 More actions · Answer 2

no. the example also shows "and" and "is" also. It is not always "to"

Lowell SSC Guru Points: 323518 More actions · Answer 3

i looked at this, and it seems the best solution would be a CTE so you can use regular expressions, right?

then you can find the word breaks before and after the occurrance of the @ symbol;

it's a bit harder to do with pure TSQL, but possible, i think.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Geoff A SSChampion Points: 11511 More actions · Answer 4

i submitted my solution;

create table #mytable (Items varchar(8000))

declare @mystring nvarchar(250)

declare db_crsr cursor for

select Content from TC41

open db_crsr

fetch next from db_crsr into @mystring

while @@fetch_status = 0

begin

declare @lookforspace char(1)

set @lookforspace = ' '

declare @myidx int

declare @slice varchar(8000)

select @myidx = 1

if len(@mystring)<1 or @mystring is null return

while @myidx!= 0

begin

set @myidx = charindex(@lookforspace,@mystring)

if @myidx!=0

set @slice = left(@mystring,@myidx - 1)

else

set @slice = @mystring

if(len(@slice)>0)

insert into #mytable(Items) values(@slice)

set @mystring = right(@mystring,len(@mystring) - @myidx)

if len(@mystring) = 0 break

end

fetch next from db_crsr into @mystring

end

close db_crsr

deallocate db_crsr

;

WITH

cteFindMax AS

(

SELECT count(*) OVER (PARTITION BY Items) AS Occurances,

*

FROM #mytable where Items like '%@%' and Items like '%.%'

)

SELECT DISTINCT Items as Email, Occurances as Occurances

FROM cteFindMax

order by Occurances DESC

drop table #mytable