Parse the username in the string T-SQL

Question

Parse the username in the string T-SQL

SQLKiller

SSC Veteran

Points: 246
More actions
December 18, 2015 at 11:11 am

#310281

Hello Experts,
I am trying to pull a user name in the below text message using T-SQL. I am really close but some how its not stripping the user name correctly.
Any guidance please.
Message Column Data: The job succeeded. The Job was invoked by User DOMAIN\USER1. The last step to run was step 1 (TestJob).
Below is my sql query I am using to pull DOMAIN\USER1
SELECT USERNAME=SUBSTRING(ltrim(rtrim(jh.message)),
CHARINDEX('was invoked by user', ltrim(rtrim(jh.message))) + LEN('was invoked by user')+1
, CHARINDEX('. The last step',ltrim(rtrim(jh.message)))
-CHARINDEX('was invoked by user', ltrim(rtrim(jh.message)))- LEN('was invoked by user')-1) FROM dbo.sysjobhistory jh

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

SQLKiller SSC Veteran Points: 246 More actions · Answer 1

Some how in the script CHARINDEX('. The last step',ltrim(rtrim(jh.message))) was not returning exact value as 63 but was giving 0. I fixed this by removing '.' in the above script and was able to parse the user name correctly.

SELECT USERNAME=

SUBSTRING(LTRIM(RTRIM(Hist.message)),

CHARINDEX('was invoked by User ',ltrim(rtrim(Hist.message)))+LEN('was invoked by User ')

,CHARINDEX(' The last step',ltrim(rtrim(Hist.message)))-CHARINDEX('was invoked by User ',ltrim(rtrim(Hist.message)))

-Len('was invoked by User')-2)

FROM sysjobs JOB

INNER JOIN sysjobhistory HIST ON HIST.job_id = JOB.job_id

WHERE JOB.name = '??jobname??' and Hist.step_id=0

ORDER BY HIST.run_date, HIST.run_time

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

This might be simpler.

SELECT LEFT( trunc.message, CHARINDEX(' ', trunc.message)), *

FROM dbo.sysjobhistory jh

CROSS APPLY( SELECT SUBSTRING( message, 49, 4000)) trunc(message)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2