I was working on a project which involved data migration and I needed a way to extract email addresses out of the notes field. So here is what I came up with.
Create FUNCTION svf_ExtractEmailAddress
(
@notes varchar(1000), @incident int /*1 to get the first occurance, 2 to get the second, etc*/
)
RETURNS varchar(100)
AS
BEGIN
declare @result varchar(100), @iStart int, @iMid int, @iEnd int, @temp varchar(1000), @incidentCounter int
--replace newline (10/13) and tab(9) with space
set @notes = REPLACE(REPLACE(REPLACE(@notes,CHAR(13),' '), char(10), ' '), char(9), ' ')
set @incidentCounter=1
set @iMid = patindex('%[A-Z1-9]@[A-Z1-9]%', @notes) --the location of the character righth before the @ sign
while (@incidentCounter < @incident and @iMid > 0)
Begin
set @notes = substring(@notes, @iMid +1, 1000)
set @iMid = patindex('%[A-Z1-9]@[A-Z1-9]%', @notes) --the location of the character righth before the @ sign
set @incidentCounter = @incidentCounter+1
End
if (@iMid =0) return ''
--locate the first space before the at symbol
set @temp = substring(@notes,0, @iMid )-- temp contains everything BEFORE the @sign
set @iStart = charindex(' ', @temp)
set @temp = substring(@notes,@iStart, @iMid-@iStart )
while (charindex(' ', @temp)>0)
Begin
set @iStart = @iStart + charindex(' ', @temp)
set @temp = substring(@notes,@iStart, @iMid-@iStart)
End
--locate the first space after the @ symbol
set @temp = substring(@notes,@iMid, 1000 ) -- temp contains everything AFTER the @sign
set @iEnd = patindex('%[ ,:;'+CHAR(13)+CHAR(10)+']%', @temp)
set @iEnd =
case when @iEnd=0 then 1000
else @iMid + @iEnd -1 end
/*
set @result = '@iMid: ' + convert(varchar(9), @iMid)
+ ' @iStart: ' + convert(varchar(9), @iStart)
+ ' @iEnd: ' + convert(varchar(9), @iEnd)
+ ' email: ' + substring (@notes, @istart, @iEnd - @istart)
--*/
set @result = substring (@notes, @istart, @iEnd - @istart)
RETURN @result
END
Example usage:
declare @note varchar(1000) set @note= 'bird@gmail.com hello happy chipmunk@yahoo.com, you are nice guy so dog@gmail.com cat@gmail.com'
select e1=dbo.svf_ExtractEmailAddress(@note, 1), e2=dbo.svf_ExtractEmailAddress(@note, 2),
e3=dbo.svf_ExtractEmailAddress(@note, 3), e4=dbo.svf_ExtractEmailAddress(@note, 4),
e5=dbo.svf_ExtractEmailAddress(@note, 5)
And here is the result:
