Sql help to find username in a data set-CodePudding

Data

     row  data
      1  (172.32.313.20:5892) User 'ant\john' requested 
      2  User ant\john logged on from 172.31.13.2129
      3  user=ant\john domain=ant.amazon.com server=172.31.19.541 protocol=LDAPS result=0:Success

I need to pull the username (john) from this dataset .

select message,replace(TRIM(split_part(split_part(message, 'requested', 1), 'User ', 2)), 'ant\\', '') username1,
replace(TRIM(split_part(split_part(message, 'requested', 1), 'user=', 2)), 'ant\\', '')username2
from test_kemp_log.archive
where message like '%john%'

Is there a better way to extract User(user) ant/ information from dataset?

CodePudding user response：

The following could work in your case.

SELECT regexp_substr(data, 'ant\\\\'||'([a-z] )', 1,1, 'e'), data From test_kemp_log.archive
WHERE message like '%john%';

'e' is for extracting the first group. In our case, it's the first thing that matches ([a-z] ) after "ant\".