Hi, @Incrypt-_, @neil-schipper, @peterjones and All,
I slightly modified my two regex S/R. So :
Regarding — Pass 8 – Part 1
SEARCH (?s-i)\QEXEC msdb.dbo.sp_add_job @job_name=N'\E(\w+).+?\K^---- END_OF_JOB
REPLACE \1$0
In the replacement, you can use the $0 syntax wich corresponds to the complete regex match, so the literal string ---- END_OF_JOB
Regarding ---- Pass 8 – Part 2
SEARCH (?-is)\QEXEC msdb.dbo.sp_add_jobstep @job_\E\Kid=@jobId(?=(?s:.+?)^(\w+)---- END_OF_JOB\R)|^(\w+)---- END_OF_JOB\R
REPLACE ?2---- END_OF_JOB\r\n:name=N'\1'
As in the previous regex, we search for word characters ( Letters, digits and underscore only, representing the job_name ), at beginning of line(s), right before the ---- END_OF_JOB string
I also used the \K syntax, instead of the positive look-behind feature. Remainder : the \K syntax means that you must, necessarily, use the Replace All button ( the Replace button won’t work ! )
Now, here is a full explanation of the provided regexes, in this last version :
First search :
At beginning the part (?s-i) are in-line modifiers which means :
The text is seen as a single line. So the . regex symbol represents any single character ( Standard AND EOL chars )
The text must respect the case ( -i means non-insensitive search )
Then, the part \QEXEC msdb.dbo.sp_add_job @job_name=N'\E looks for the string EXEC msdb.dbo.sp_add_job @job_name=N’, with this exact case. Everything between the \Q and \E escape sequences is simply considered as literal text !
Then the part (\w+) searches for, at least, one word character ( the job_name ) and stores it as group 1
Finally, the part .+?\K^---- END_OF_JOB looks for the smallest range, .+?, of any char, till the string ^---- END_OF_JOB, beginning a line
In addition, the \K syntax cancels any previous match, so far and resets the regex engine location. So, the final match is only the literal string ---- END_OF_JOB
First replacement : the literal string ^---- END_OF_JOB is replaced with :
The job_name ( The group \1 )
Then, the string ---- END_OF_JOB ( $0 syntax, representing the complete searched match )
Second search : it contains two independant searches :
The regex search (?-is)\QEXEC msdb.dbo.sp_add_jobstep @job_\E\Kid=@jobId(?=(?s:.+?)^(\w+)---- END_OF_JOB\R)
The regex search ^(\w+)---- END_OF_JOB\R
At beginning the part (?-is) are in-line modifiers which stand for each alternative and mean :
The text must respect the case ( -i means non-insensitive search )
The text is not seen as a single line. So the . regex symbol represents any single standard character only ( not the EOL chars )
Then, the part \QEXEC msdb.dbo.sp_add_jobstep @job_\E looks for the string \QEXEC msdb.dbo.sp_add_jobstep @job_\E, with this exact case. Everything between the \Q and \E escape sequences is simply considered as literal text !
The \K syntax cancels any previous match, so far and resets the regex engine location. So the final match is only the literal string id=@jobId, but…
ONLY IF a smallest range of any char ( ?s modifier restricted to the non-capturing group (?s:.+?) ) till a job_name, beginning a line, followed with the string ---- END_OF_JOB, with this exact case and its line-endings can be found further on, due to the look-ahead structure (?=(?s:.+?)^(\w+)---- END_OF_JOB\R)
The last alternative ^(\w+)---- END_OF_JOB\R) simply looks for some word chars ( the job_name ), beginning a line, followed with the literal string ---- END_OF_JOB and its line-endings
Second replacement : it uses a special Boost feature, called a conditional replacement :
IF the group 2 exists ( so the second alternative ), the job_name, beginning a line and the string ---- END_OF_JOB, followed with its line-endings is just replaced with the literal string ---- END_OF_JOB and \r\n ( replacement part before the colon char )
ELSE ( case of the first alternative ), the literal string id=@jobId, located after any EXEC msdb.dbo.sp_add_jobstep @job_ text, is replaced with the literal string name=N', followed with the group 1 ( the job_name ) and finally a ' char ( replacement part after the colon char )
Best Regards,
guy038
