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

  .

• crazy4sql

  SSCoach

  Points: 19590

  More actions

  October 18, 2010 at 4:06 am

  #1238288

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

  October 19, 2010 at 11:18 am

  #1239221

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

• Lowell

  SSC Guru

  Points: 323522

  More actions

  October 19, 2010 at 12:22 pm

  #1239267

  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

  October 19, 2010 at 1:54 pm

  #1239312

  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