Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT dbo.getEmailAddresses('this is misc andrew@g.com')
- --output andrew@g.com
- SELECT dbo.getEmailAddresses('this is misc andrew@g.com and a medium text returning %John@acme.com')
- --output andrew@g.com; John@acme.com
- CREATE FUNCTION [dbo].[getEmailAddresses] (@Str varchar(8000))
- RETURNS varchar(8000) AS
- BEGIN
- declare @i int, @StartPos int,@AtPos int,@EndPos int;
- declare @MailList varchar(8000);
- declare @MailTempl varchar(100);
- SET @MailList=NULL;
- SET @MailTempl='[A-Za-z0-9_.-]'; --allowing symbols in e-mail not including @
- SET @AtPos=PATINDEX('%'+@MailTempl+'@'+@MailTempl+'%',@Str)+1;
- While @AtPos>1
- begin
- --go left
- SET @i=@AtPos-1;
- while (substring(@Str,@i,1) like @MailTempl) SET @i=@i-1;
- SET @StartPos=@i+1;
- --go right
- SET @i=@AtPos+1;
- while (substring(@Str,@i,1) like @MailTempl) SET @i=@i+1;
- SET @EndPos=@i-1;
- SET @MailList=isnull(@MailList+';','')+Substring(@Str,@StartPos,@EndPos-@StartPos+1);
- --prepare for the next round
- SET @Str=substring(@Str,@EndPos+1,LEn(@Str));
- SET @AtPos=PATINDEX('%'+@MailTempl+'@'+@MailTempl+'%',@Str)+1;
- end;
- RETURN @MailList;
- END
- create function getEmailAddresses
- (
- @test varchar(max)
- )
- returns varchar(max)
- As
- BEGIN
- declare @emaillist varchar(max)
- --SET @test=' this is it by it a@b.com dsdkjl dsaldkj a@b.com dasdlk c@bn.com dsafhjkf anand@p.com d fdajf s@s.com .'
- ;WITH CTE as(
- select reverse(left(reverse(left(@test,CHARINDEX('.com',@test)+3)),charindex(' ',reverse(left(@test,CHARINDEX('.com',@test)+3))))) as emailids,
- right(@test,len(@test)-(CHARINDEX('.com',@test)+3)) rem
- union all
- select CASE WHEN len(rem)>2 then reverse(left(reverse(left(rem,CHARINDEX('.com',rem)+3)),charindex(' ',reverse(left(rem,CHARINDEX('.com',rem)+3))))) else 'a' end as emailids ,
- CASE WHEN len(rem) > 2 then right(rem,len(rem)-(CHARINDEX('.com',rem)+3)) else 'a' end rem
- from CTE where LEN(rem)>2
- )
- select @emaillist =STUFF((select ','+emailids from CTE for XML PATH('')),1,1,'')
- return @emaillist
- END
- select dbo.getEmailAddresses('this is it by it a@b.com dsdkjl dsaldkj a@b.com dasdlk c@bn.com dsafhjkf anand@p.com d fdajf s@s.com .')
- Declare @str varchar(max) = 'this is misc andrew@g.com and a medium text returning John@acme.com'
- ;With Cte AS(
- SELECT
- Items = Split.a.value('.', 'VARCHAR(100)')
- FROM
- (
- SELECT
- CAST('<X>' + REPLACE(@str, ' ' , '</X><X>') + '</X>' AS XML) AS Splitdata
- ) X
- CROSS APPLY Splitdata.nodes('/X') Split(a) )
- SELECT Email = STUFF((
- SELECT ';'+ Items
- FROM Cte
- Where Items
- LIKE '[A-Z0-9]%[@][A-Z]%[.][A-Z]%'
- FOR XML PATH('')),1,1,'')
- andrew@g.com;John@acme.com
- static void Main(string[] args)
- {
- string str = "this is misc andrew@g.com and a medium text returning John@acme.com";
- var result = GetValidEmails(str).Aggregate((a,b)=>a+";" + b);
- Console.WriteLine(result);
- Console.ReadKey();
- }
- private static List<string> GetValidEmails(string input)
- {
- List<string> lstValidEmails = new List<string>();
- string regexPattern = @"^[A-Z0-9._%+-]+@[A-Z0-9.-]+.[A-Z]{2,4}$";
- foreach (string email in input.Split(' '))
- {
- if (new Regex(regexPattern, RegexOptions.IgnoreCase).IsMatch(email))
- {
- lstValidEmails.Add(email);
- }
- }
- return lstValidEmails;
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement