Regex to extract certain part of user comments in analytics

[sb17gds]

Hi team,

When someone gets terminated from certification, it generates a comment like this: 

Users are being terminated through Certification -#####31579#####[Test_UM_Employee_V22 - 00569565 (FirstName LastName)] on -Thu Oct 10 09:31:20 UTC 2024#####Reason: User Terminated and Certification Locked (UM Campaign)

The client needs us to extract the username of the certifying user from this string - however whenever i try to use charindex it says invalid:

For example i tried to extract everything between the square brackets:

select u.username as 'USERNAME', u.employeeid as 'USEREMPLOYEEID', a.username as 'MANAGER', u.firstname as 'FIRSTNAME', u.lastname as 'LASTNAME', a.firstname as 'MFIRSTNAME', a.lastname as 'MLASTNAME', u.termdate as 'TERMDATE', SUBSTRING(u.comments, CHARINDEX('[', u.comments) + 1, CHARINDEX(']', u.comments) - CHARINDEX('[', u.comments) - 1) as 'UCOMMENTS' from users u, users a where u.manager = a.userkey and u.statuskey = 0 and u.comments like '%Certification%' and u.termdate >= NOW() - INTERVAL 1 DAY

but its not working. Is there a regex we can use in this case?

Or alternatively, some other user property that should store who has triggered the termination. The updateuser attribute is storing admin userkey.

Thanks in advance

[rushikeshvartak · Accepted solution]

SELECT U.USERNAME                                                    AS
       'USERNAME',
       U.EMPLOYEEID                                                  AS
       'USEREMPLOYEEID',
       A.USERNAME                                                    AS
       'MANAGER',
       U.FIRSTNAME                                                   AS
       'FIRSTNAME',
       U.LASTNAME                                                    AS
       'LASTNAME',
       A.FIRSTNAME                                                   AS
       'MFIRSTNAME',
       A.LASTNAME                                                    AS
       'MLASTNAME',
       U.TERMDATE                                                    AS
       'TERMDATE',
       SUBSTRING_INDEX(SUBSTRING_INDEX(U.COMMENTS, '[', -1), ']', 1) AS
       'UCOMMENTS'
FROM   USERS U
       JOIN USERS A
         ON U.MANAGER = A.USERKEY
WHERE  U.STATUSKEY = 0
       AND U.COMMENTS LIKE '%Certification%'
       AND U.TERMDATE >= NOW() - INTERVAL 1 DAY; 

[sb17gds]

Thank you so much, this did the trick!