Extract All Instances of String into Concatenated Result

Question

Using SQL Server 2014, I'm wanting to search within a field and return all instances of a string that is found, plus the following word. For example, the text in the column may be:

"exec sproc1 and then some more text here and then maybe execute sproc2 exec storedproc3 and maybe exec sproc1"

I'd like to elegantly return "sproc1, sproc2, storedproc3, sproc1", as each was the word following either exec or execute (as delimited by spaces). As you can see in the example, the leading word may vary, as may the length of the sproc name. I've been able to return the first usage of exec/execute; my issue is that sometimes there are multiple (see below).

REPLACE(REPLACE(CASE
        WHEN [sJSTP].[subsystem]='TSQL' AND CHARINDEX('EXECUTE',[sJSTP].[command],1)>0
            THEN SUBSTRING([sJSTP].[command],CHARINDEX('EXECUTE',[sJSTP].[command],1)+8,
                IIF(
                CHARINDEX(' ',[sJSTP].[command],CHARINDEX('EXECUTE',[sJSTP].[command],1)+8)>0,
                CHARINDEX(' ',[sJSTP].[command],CHARINDEX('EXECUTE',[sJSTP].[command],1)+8)-CHARINDEX('EXECUTE',[sJSTP].[command],1)-8,
                LEN([sJSTP].[command])))
        WHEN [sJSTP].[subsystem]='TSQL' AND CHARINDEX('EXEC',[sJSTP].[command],1)>0 AND CHARINDEX('DCEXEC',[sJSTP].[command],1)<=0
            THEN SUBSTRING([sJSTP].[command],CHARINDEX('EXEC',[sJSTP].[command],1)+5,
                IIF(
                CHARINDEX(' ',[sJSTP].[command],CHARINDEX('EXEC',[sJSTP].[command],1)+5)>0,
                CHARINDEX(' ',[sJSTP].[command],CHARINDEX('EXEC',[sJSTP].[command],1)+5)-CHARINDEX('EXEC',[sJSTP].[command],1)-5,
                LEN([sJSTP].[command])))
    END,'[',''),']','') AS sprocname

The ultimate use of this is parsing job commands from the msdb..sysjobsteps table to see what stored procedures are being used.

Edit: Add sample data

Sample 1: exec quarterly_run 1, 'BW' exec quarterly_run_2 1, 'QR ' exec quarterly_run 2, 'VAS' exec quarterly_run 1, 'WR' exec quarterly_run 3, 'RW' exec quarterly_run_2 1, 'ASF' exec quarterly_run_3 1, 'ALL'

Sample 2: declare @rundate datetime, @rptqtr datetime, @qtr int

set @rundate = getdate() set @rptqtr = '06/30/2016'

set @qtr = (select datediff(quarter,@rptqtr,@rundate))

exec quarterly_extract @qtr

Sample 3: exec Daily_Sync_Process exec Daily_Process

Answer 1

Just another inline option, and not limited to 8K

Example

Declare @YourTable table (ID int,SomeCol varchar(max))
Insert into @YourTable values
 (1,'exec quarterly_run 1, ''BW'' exec quarterly_run_2 1, ''QR '' exec quarterly_run 2, ''VAS'' exec quarterly_run 1, ''WR'' exec quarterly_run 3, ''RW'' exec quarterly_run_2 1, ''ASF'' exec quarterly_run_3 1, ''ALL''')
,(2,'declare @rundate datetime, @rptqtr datetime, @qtr int

set @rundate = getdate() set @rptqtr = ''06/30/2016''

set @qtr = (select datediff(quarter,@rptqtr,@rundate))

exec quarterly_extract @qtr
')
,(3,'exec Daily_Sync_Process exec Daily_Process')

;with cte as (
Select A.ID
      ,C.*
 From  @YourTable A
 Cross Apply (values (replace(replace(SomeCol,char(13),' '),char(10),' '))) B(CleanString)
 Cross Apply (
                Select RetSeq,RetVal = case when Lag(RetVal,1) over (Order by RetSeq) in ('Exec','Execute') then RetVal else null end
                From (
                        Select RetSeq = Row_Number() over (Order By (Select null))
                              ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                        From  (Select x = Cast('<x>' + replace((Select replace(CleanString,' ','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                        Cross Apply x.nodes('x') AS B(i)
                     ) C1
             ) C
)
Select A.ID
      ,NewString = Stuff((Select ', ' +RetVal From cte Where ID=A.ID Order By RetSeq For XML Path ('')),1,2,'') 
 From cte A
 Group By A.ID

Returns

ID  NewString
1   quarterly_run, quarterly_run_2, quarterly_run, quarterly_run, quarterly_run, quarterly_run_2, quarterly_run_3
2   quarterly_extract
3   Daily_Sync_Process, Daily_Process