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

My table:

Output I need:

CodePudding user response：

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<[email protected]> updated profile of admin Sam Welson<[email protected]>';

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<[email protected]> updated profile of admin Sam Welson<[email protected]> | James Johnson.[[email protected]].Sam Welson.[[email protected]] | James Johnson | [email protected] | Sam Welson | [email protected] |
 ------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------- --------------- ------------------------ ------------ --------------------- 

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