TSQL Challenge 41 - Extract email addresses from text data

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


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


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

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


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

  • 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


    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


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

    if @myidx!=0

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


    set @slice = @mystring


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

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

    if len(@mystring) = 0 break


    fetch next from db_crsr into @mystring


    close db_crsr

    deallocate db_crsr



    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

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

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