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

• SQLKiller

  SSC Veteran

  Points: 246

  More actions

  December 18, 2015 at 12:01 pm

  #1846861

  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

  December 18, 2015 at 2:26 pm

  #1846886

  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