Need help parsing this string

• oradbguru

  Hall of Fame

  Points: 3126

  More actions

  November 2, 2018 at 5:11 pm

  #380692

  Hello, I have been trying to parse the below string and keep getting errors.

  JONES logon to Core Services 13,0,2,175 on Wkst A123mABCm04, AppServer SRVmW12mABWEB, Session 5602830, DriverVersion: MSSqlServer (11.00.300)                  

  When I try to parse out SRVmW12mABWEB from the above string, I keep getting the following error.

  Msg 537, Level 16, State 2, Line 1
  Invalid length parameter passed to the LEFT or SUBSTRING function.

  Can you please provide a solution for this?  The string is a value in a varchar column of a table.

  Thank you for your expertise.
  David

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 2, 2018 at 6:01 pm

  #2012298

  oradbguru - Friday, November 2, 2018 5:11 PM

  Hello, I have been trying to parse the below string and keep getting errors.

  JONES logon to Core Services 13,0,2,175 on Wkst A123mABCm04, AppServer SRVmW12mABWEB, Session 5602830, DriverVersion: MSSqlServer (11.00.300)                  

  When I try to parse out SRVmW12mABWEB from the above string, I keep getting the following error.

  Msg 537, Level 16, State 2, Line 1
  Invalid length parameter passed to the LEFT or SUBSTRING function.

  Can you please provide a solution for this?  The string is a value in a varchar column of a table.

  Thank you for your expertise.
  David

  Try the substring in the query below replacing the variable with your column in the table.
  
declare @TestString varchar(128) = 'JONES logon to Core Services 13,0,2,175 on Wkst A123mABCm04, AppServer SRVmW12mABWEB, Session 5602830, DriverVersion: MSSqlServer (11.00.300)';

  select
@TestString
, patindex('%AppServer%',@TestString)
, charindex(',',@TestString,patindex('%AppServer%',@TestString))
, substring(@TestString,patindex('%AppServer%',@TestString) + 10,charindex(',',@TestString,patindex('%AppServer%',@TestString)) - (patindex('%AppServer%',@TestString) + 10));