Find Multiple Substring from a String Using SQL

I have a column log. I need to extract values from that log column and make 4 new columns

My table:

Output I need:

Answer

If the string really always follows exactly that format, you can use something funky like this:

DECLARE @test NVARCHAR(1000)
    = N'Super admin James Johnson<[e-mail]> updated profile of admin Sam Welson<[e-mail]>';

SELECT *
,      PARSENAME(repl.val, 4) AS AdminName
,      PARSENAME(repl.val, 3) AS AdminEmail
,      PARSENAME(repl.val, 2) AS UpdateName
,      PARSENAME(repl.val, 1) AS UpdateEmail
FROM (
         VALUES (@test)
     ) AS X (T)
OUTER APPLY (
                VALUES (REPLACE(
                                   REPLACE(
                                              REPLACE(
                                                         REPLACE(X.T, 'Super admin ', '') --Remove this completely
                                                     ,   ' updated profile of admin ' --Replace this with a .
                                                     ,   '.'
                                                     )
                                          ,   '<'   --Replace this with a . and [ (so the . in the email isn't parsed)
                                          ,   '.['
                                          )
                               ,   '>' --Reaplace with ] to complemanted the previous ]
                               ,   ']'
                               )
                       )
            ) AS repl (val);

Which gives:

+------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------+---------------+------------------------+------------+---------------------+
|                                                     T                                                      |                                   val                                   |   AdminName   |       AdminEmail       | UpdateName |     UpdateEmail     |
+------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------+---------------+------------------------+------------+---------------------+
| Super admin James Johnson<[e-mail]> updated profile of admin Sam Welson<[e-mail]> | James Johnson.[[e-mail]].Sam Welson.[[e-mail]] | James Johnson | [e-mail] | Sam Welson | [e-mail] |
+------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------+---------------+------------------------+------------+---------------------+

This abuses the PARSENAME function, which is meant to parse 4 part named objects (server_name.database_name.schema_name.object_name).