string - MS SQL - 如何从大字符串中获取以逗号/分号分隔的电子邮件地址列表

Question

如何提取字符串中可用的所有电子邮件地址的列表，作为逗号/分号分隔的列表

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

Answer 1

这是一个 UDF。更正此行 SET @MailTempl='[A-Za-z0-9_.-]如果您需要其他(例如电子邮件地址中的非英文符号)。 Here is a full list of allowed characters

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