SQL Query help!

• sqldba20

  Ten Centuries

  Points: 1310

  More actions

  November 6, 2018 at 5:32 am

  #368355

  Folks:

  I need help with a query. I have a column in which we store the stored procedures executed history with the parameters passed. I want to parse the data and get only the stored procedure names from the column. 

  exec spname1 867, "name"
  exec spname2 "mgr"
  exec spname1 678, "name2"
  exec spname3 "John", "Dan", 6456
  exec spname1 4567, "test"

  I want the Output to be:

  exec spname1
  exec spname2
  exec spname1
  exec spname3
  exec spname1

  Thanks!

• debasis.yours

  Hall of Fame

  Points: 3516

  More actions

  November 6, 2018 at 5:39 am

  #2012505

  Hi,

  As you are using SQL 2017 forum, you can make use of STRING_SPLIT() function passing ' ' as the delimiter I think.

  https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017

  Thanks.

• Eirikur Eiriksson

  SSC Guru

  Points: 182946

  More actions

  November 6, 2018 at 5:39 am

  #2012506

  sqldba20 - Tuesday, November 6, 2018 5:32 AM

  Folks:

  I need help with a query. I have a column in which we store the stored procedures executed history with the parameters passed. I want to parse the data and get only the stored procedure names from the column. 

  exec spname1 867, "name"
  exec spname2 "mgr"
  exec spname1 678, "name2"
  exec spname3 "John", "Dan", 6456
  exec spname1 4567, "test"

  I want the Output to be:

  exec spname1
  exec spname2
  exec spname1
  exec spname3
  exec spname1

  Thanks!

  This should get you going
  

  DECLARE @SAMPLE VARCHAR(100) = 'exec spname2 "mgr"';
SELECT LEFT(@SAMPLE,CHARINDEX(CHAR(32),@SAMPLE,6) - 1);

  Output
  exec spname2