Extract Emails that Lying Between Special Characters Using Regex in SQL-CodePudding

How do I extract only email from this certain pattern of string using regex in SQL?

What I have : tb_1

Logmessage
Alan Robert <[email protected]> was assigned to <[email protected]> and <[email protected]>
Alan Robert <[email protected]> was unassigned to <[email protected]> and <[email protected]>

What I want: tb_2

email_1	email_2	email_3
[email protected]	[email protected]	[email protected]
[email protected]	[email protected]	[email protected]

I already have a solution for this but the tb_1 table has a huge amount of rows so my query output takes too much time. That's why I thought maybe regex would be more time-saving.

My query:

with cte as(
Select replace(replace(replace(replace(right(@logmessage, len(logmessage)-charindex('<', logmessage) 1), 
                                         Case when logmessage like '%unassigned%' Then ' was unassigned to ' 
                                       When logmessage like '%assigned%' then ' was assigned to ' End , '.'),' and ', '.'), 
                                                                        '<', '['  ),'>', ']') logmessage
From tb_1)

Select 
       PARSENAME(logmessage, 3) AS email_3,
       PARSENAME(logmessage, 3) AS email_2,
       PARSENAME(logmessage, 1) AS email_1
From cte

CodePudding user response：

With the use of a helper function

Example or dbFiddle

Declare @YourTable Table (LogID int,[Logmessage] varchar(500))  Insert Into @YourTable Values 
 (1,'Alan Robert <[email protected]> was assigned to <[email protected]> and <[email protected]>')
,(2,'Alan Robert <[email protected]> was unassigned to <[email protected]> and <[email protected]>')

Select A.LogID
      ,B.* 
 From @YourTable A
 Cross Apply [dbo].[tvf-Str-Extract-JSON](LogMessage,'<','>') B

Results

LogID   RetSeq  RetVal
1       1       [email protected]
1       2       [email protected]
1       3       [email protected]
2       1       [email protected]
2       2       [email protected]
2       3       [email protected]

It would then be a small matter to pivot the results

The TVF if interested

CREATE FUNCTION [dbo].[tvf-Str-Extract-JSON] (@String varchar(max),@Delim1 varchar(100),@Delim2 varchar(100))
Returns Table 
As
Return (  

    Select RetSeq = row_number() over (order by RetSeq)
          ,RetVal = left(RetVal,charindex(@Delim2,RetVal)-1)
    From  (
            Select RetSeq = [Key] 1
                  ,RetVal = trim(Value)
             From  OpenJSON( '["' replace(string_escape(@String,'json'),@Delim1,'","') '"]' )

          ) C1
    Where charindex(@Delim2,RetVal)>1

)