REGEX: How can I get cursor like behavior from a regex expression? (Somewhat working needs help)

[InCrypt _]

@incrypt-_

---

Test File (I can’t upload yet) Also, from TestJob4 I clipped out steps 11 - 99 just to save space.

USE [msdb]
GO
----------- Begin Job Step Name testing --------------



/****** Object:  Job [TestJob1]    Script Date: 4/5/2022 12:31:53 PM ******/

EXEC msdb.dbo.sp_add_job @job_name=N'TestJob1', 
		@enabled=0
--...
--12 - 14 Rows of job Config info
-- ...
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_1', 
		@step_id=1, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_2]    Script Date: 4/5/2022 12:31:53 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_2', 
		@step_id=2, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_3]    Script Date: 4/5/2022 12:31:53 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_3', 
		@step_id=3, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_4]    Script Date: 4/5/2022 12:31:53 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_4', 
		@step_id=4, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_5]    Script Date: 4/5/2022 12:31:53 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_5', 
		@step_id=5, 
--...
--12 - 14 Rows of job step info
-- ...





GO

/****** Object:  Job [TestJob2]    Script Date: 4/5/2022 12:31:54 PM ******/

EXEC msdb.dbo.sp_add_job @job_name=N'TestJob2', 
		@enabled=1
--...
--12 - 14 Rows of job Config info
-- ...
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_1', 
		@step_id=1, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_2]    Script Date: 4/5/2022 12:31:54 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_2', 
		@step_id=2, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_3]    Script Date: 4/5/2022 12:31:54 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_3', 
		@step_id=3, 
--...
--12 - 14 Rows of job step info
-- ...





GO

/****** Object:  Job [TestJob3]    Script Date: 4/5/2022 12:31:54 PM ******/

EXEC msdb.dbo.sp_add_job @job_name=N'TestJob3', 
		@enabled=1
--...
--12 - 14 Rows of job Config info
-- ...
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_1', 
		@step_id=1, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_2]    Script Date: 4/5/2022 12:31:54 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_2', 
		@step_id=2, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_3]    Script Date: 4/5/2022 12:31:54 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_3', 
		@step_id=3, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_4]    Script Date: 4/5/2022 12:31:54 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_4', 
		@step_id=4, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_5]    Script Date: 4/5/2022 12:31:54 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_5', 
		@step_id=5, 
--...
--12 - 14 Rows of job step info
-- ...





GO
/****** Object:  Job [TestJob4]    Script Date: 4/5/2022 12:32:14 PM ******/

EXEC msdb.dbo.sp_add_job @job_name=N'TestJob4', 
		@enabled=1
--...
--12 - 14 Rows of job Config info
-- ...
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_1', 
		@step_id=1, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_2]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_2', 
		@step_id=2, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_3]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_3', 
		@step_id=3, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_4]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_4', 
		@step_id=4, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_5]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_5', 
		@step_id=5, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_6]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_6', 
		@step_id=6, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_7]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_7', 
		@step_id=7, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_8]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_8', 
		@step_id=8, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_9]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_9', 
		@step_id=9, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_10]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_10', 
		@step_id=10, 
--...
--12 - 14 Rows of job step info
-- ...



/****** Object:  Step [TestJobStep_100]    Script Date: 4/5/2022 12:32:18 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_100', 
		@step_id=100, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_101]    Script Date: 4/5/2022 12:32:18 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_101', 
		@step_id=101, 
--...
--12 - 14 Rows of job step info
-- ...


GO

[PeterJones]

@incrypt-_ said in REGEX: How can I get cursor like behavior from a regex expression? (Somewhat working needs help):

I’m a bit stuck here, and hoping the community can throw me a life line.

What you’ve described is a job for programming, not for regex. If you want, you could use the PythonScript plugin inside Notepad++ to give your program (script) direct access to the contents of the Notepad++ editor from a python script that you would have to write to solve the problem.

Test File (I can’t upload yet)

“yet” is a misnomer. The forum doesn’t have generic file storage. It allows you to paste an image into the post, but you cannot attach any other kind of file.

[Neil Schipper]

@incrypt-_

Hi. I believe I have a solution.

First off, for development purposes, I condensed the data you provided, maintaining the crucial elements while making it much less painful to work with:

USE [msdb]
GO
EXEC msdb.dbo.sp_add_job @job_name=N'TestJob1',h		@enabled=0
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_1',h		@step_id=1, 
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_2',h		@step_id=2, 
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_3',h		@step_id=3, 
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_4',h		@step_id=4, 
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_5',h		@step_id=5, 
GO
EXEC msdb.dbo.sp_add_job @job_name=N'TestJob2',h		@enabled=1
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_1',h		@step_id=1, 
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_2',h		@step_id=2, 
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_3',h		@step_id=3, 
GO
EXEC msdb.dbo.sp_add_job @job_name=N'TestJob3',h		@enabled=1
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_1',h		@step_id=1, 
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_2',h		@step_id=2, 
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_3',h		@step_id=3, 
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_4',h		@step_id=4, 
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_5',h		@step_id=5, 
GO
EXEC msdb.dbo.sp_add_job @job_name=N'TestJob4',h		@enabled=1
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_1',h		@step_id=1, 
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_2',h		@step_id=2, 
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_3',h		@step_id=3, 
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_4',h		@step_id=4, 
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_5',h		@step_id=5, 
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_6',h		@step_id=6, 
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_7',h		@step_id=7, 
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_8',h		@step_id=8, 
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_9',h		@step_id=9, 
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_10',h		@step_id=10, 
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_100',h		@step_id=100, 
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_101',h		@step_id=101, 
GO

I developed this regex:

Fi: (?s)(?<=^\QEXEC msdb.dbo.sp_add_job \E)(@job_name=N')(\w+)((?:(?!\r\nGO).)*?)@job_id=@jobId
Re: \1\2\3@job_name=N'\2'

which produces this output:

USE [msdb]
GO
EXEC msdb.dbo.sp_add_job @job_name=N'TestJob1',h		@enabled=0
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob1', @step_name=N'TestJobStep_1',h		@step_id=1, 
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob1', @step_name=N'TestJobStep_2',h		@step_id=2, 
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob1', @step_name=N'TestJobStep_3',h		@step_id=3, 
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob1', @step_name=N'TestJobStep_4',h		@step_id=4, 
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob1', @step_name=N'TestJobStep_5',h		@step_id=5, 
GO
EXEC msdb.dbo.sp_add_job @job_name=N'TestJob2',h		@enabled=1
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob2', @step_name=N'TestJobStep_1',h		@step_id=1, 
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob2', @step_name=N'TestJobStep_2',h		@step_id=2, 
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob2', @step_name=N'TestJobStep_3',h		@step_id=3, 
GO
EXEC msdb.dbo.sp_add_job @job_name=N'TestJob3',h		@enabled=1
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob3', @step_name=N'TestJobStep_1',h		@step_id=1, 
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob3', @step_name=N'TestJobStep_2',h		@step_id=2, 
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob3', @step_name=N'TestJobStep_3',h		@step_id=3, 
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob3', @step_name=N'TestJobStep_4',h		@step_id=4, 
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob3', @step_name=N'TestJobStep_5',h		@step_id=5, 
GO
EXEC msdb.dbo.sp_add_job @job_name=N'TestJob4',h		@enabled=1
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob4', @step_name=N'TestJobStep_1',h		@step_id=1, 
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob4', @step_name=N'TestJobStep_2',h		@step_id=2, 
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob4', @step_name=N'TestJobStep_3',h		@step_id=3, 
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob4', @step_name=N'TestJobStep_4',h		@step_id=4, 
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob4', @step_name=N'TestJobStep_5',h		@step_id=5, 
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob4', @step_name=N'TestJobStep_6',h		@step_id=6, 
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob4', @step_name=N'TestJobStep_7',h		@step_id=7, 
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob4', @step_name=N'TestJobStep_8',h		@step_id=8, 
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob4', @step_name=N'TestJobStep_9',h		@step_id=9, 
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob4', @step_name=N'TestJobStep_10',h		@step_id=10, 
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob4', @step_name=N'TestJobStep_100',h		@step_id=100, 
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob4', @step_name=N'TestJobStep_101',h		@step_id=101, 
GO

The regex does the same required work on the messy real world data as on my condensed data.

Note that it assumes line endings are CRLF; you can use the backward P (“Show all characters”) toolbar button, or menu item View -> Show Symbol -> Show all chars, to confirm. If it deviates, an adjustment will be needed.

Each press of ReplAll operates on the next row in each section. Once all rows within a section are done, that section won’t be touched, as one would hope. After the section with the largest number of rows is done, successive presses do nothing, again as one would hope.

The main difference with the one you developed (which got you pretty close) was adding a means to abandon the active hunt when it bumps into a reliable end-of-section marker, which I took to be a newline followed by GO.

If you want to be able to convert a loaded file with one action, start a macro recording, and:

• move caret to file home
• invoke (ctl-h) and set up the Replace dialog: paste in the F & R expressions, make settings appropriate
• do one “Replace All”, Close

Stop macro recording.

Now, you can “Run a macro multiple times…”; for your test data, the needed number for obvious reasons was 12, but entering 100 causes no harm. Adjust as needed for your actual target data. (The “Run until end of file” option won’t do the whole job, I found.)

[Neil Schipper]

A slightly more refined version:

Fi: (?s)(?<=^\QEXEC msdb.dbo.sp_add_job @job_name=N'\E)(\w+)((?:(?!\r\nGO).)*?)@job_id=@jobId
Re: \1\2@job_name=N'\1'

[guy038]

Hello, @incrypt-_, @peterjones, @neil-schipper and *All,

I found out a way to avoid all the successive passes, hitting the Replace All repeatedly, untill the message No occurrence… !

So, starting with this INPUT file, below :

----------- Begin Job Step Name testing --------------



/****** Object:  Job [TestJob1]    Script Date: 4/5/2022 12:31:53 PM ******/

EXEC msdb.dbo.sp_add_job @job_name=N'TestJob1', 
		@enabled=0
--...
--12 - 14 Rows of job Config info
-- ...
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_1', 
		@step_id=1, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_2]    Script Date: 4/5/2022 12:31:53 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_2', 
		@step_id=2, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_3]    Script Date: 4/5/2022 12:31:53 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_3', 
		@step_id=3, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_4]    Script Date: 4/5/2022 12:31:53 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_4', 
		@step_id=4, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_5]    Script Date: 4/5/2022 12:31:53 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_5', 
		@step_id=5, 
--...
--12 - 14 Rows of job step info
-- ...





GO

/****** Object:  Job [TestJob2]    Script Date: 4/5/2022 12:31:54 PM ******/

EXEC msdb.dbo.sp_add_job @job_name=N'TestJob2', 
		@enabled=1
--...
--12 - 14 Rows of job Config info
-- ...
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_1', 
		@step_id=1, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_2]    Script Date: 4/5/2022 12:31:54 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_2', 
		@step_id=2, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_3]    Script Date: 4/5/2022 12:31:54 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_3', 
		@step_id=3, 
--...
--12 - 14 Rows of job step info
-- ...





GO

/****** Object:  Job [TestJob3]    Script Date: 4/5/2022 12:31:54 PM ******/

EXEC msdb.dbo.sp_add_job @job_name=N'TestJob3', 
		@enabled=1
--...
--12 - 14 Rows of job Config info
-- ...
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_1', 
		@step_id=1, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_2]    Script Date: 4/5/2022 12:31:54 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_2', 
		@step_id=2, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_3]    Script Date: 4/5/2022 12:31:54 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_3', 
		@step_id=3, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_4]    Script Date: 4/5/2022 12:31:54 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_4', 
		@step_id=4, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_5]    Script Date: 4/5/2022 12:31:54 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_5', 
		@step_id=5, 
--...
--12 - 14 Rows of job step info
-- ...





GO
/****** Object:  Job [TestJob4]    Script Date: 4/5/2022 12:32:14 PM ******/

EXEC msdb.dbo.sp_add_job @job_name=N'TestJob4', 
		@enabled=1
--...
--12 - 14 Rows of job Config info
-- ...
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_1', 
		@step_id=1, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_2]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_2', 
		@step_id=2, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_3]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_3', 
		@step_id=3, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_4]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_4', 
		@step_id=4, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_5]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_5', 
		@step_id=5, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_6]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_6', 
		@step_id=6, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_7]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_7', 
		@step_id=7, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_8]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_8', 
		@step_id=8, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_9]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_9', 
		@step_id=9, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_10]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_10', 
		@step_id=10, 
--...
--12 - 14 Rows of job step info
-- ...



/****** Object:  Step [TestJobStep_100]    Script Date: 4/5/2022 12:32:18 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_100', 
		@step_id=100, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_101]    Script Date: 4/5/2022 12:32:18 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_101', 
		@step_id=101, 
--...
--12 - 14 Rows of job step info
-- ...


GO

I use a first regex S/R which rewrites the corresponding job_name right before any word GO

• Move to the line ----------- Begin Job Step Name testing -------------- of your file

• Open the Replace dialog ( Ctrl + H )

• SEARCH (?s-i)\QEXEC msdb.dbo.sp_add_job @job_name=N'\E(\w+).+?\K^GO

• REPLACE \1GO

• Untick all box options

• Select the Regular exoression search mode

• Click, once only, on the Replace All button ( Do not use the Replace button ! )

You should obtain this temporary layout, below :

----------- Begin Job Step Name testing --------------



/****** Object:  Job [TestJob1]    Script Date: 4/5/2022 12:31:53 PM ******/

EXEC msdb.dbo.sp_add_job @job_name=N'TestJob1', 
		@enabled=0
--...
--12 - 14 Rows of job Config info
-- ...
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_1', 
		@step_id=1, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_2]    Script Date: 4/5/2022 12:31:53 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_2', 
		@step_id=2, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_3]    Script Date: 4/5/2022 12:31:53 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_3', 
		@step_id=3, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_4]    Script Date: 4/5/2022 12:31:53 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_4', 
		@step_id=4, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_5]    Script Date: 4/5/2022 12:31:53 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_5', 
		@step_id=5, 
--...
--12 - 14 Rows of job step info
-- ...





TestJob1GO

/****** Object:  Job [TestJob2]    Script Date: 4/5/2022 12:31:54 PM ******/

EXEC msdb.dbo.sp_add_job @job_name=N'TestJob2', 
		@enabled=1
--...
--12 - 14 Rows of job Config info
-- ...
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_1', 
		@step_id=1, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_2]    Script Date: 4/5/2022 12:31:54 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_2', 
		@step_id=2, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_3]    Script Date: 4/5/2022 12:31:54 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_3', 
		@step_id=3, 
--...
--12 - 14 Rows of job step info
-- ...





TestJob2GO

/****** Object:  Job [TestJob3]    Script Date: 4/5/2022 12:31:54 PM ******/

EXEC msdb.dbo.sp_add_job @job_name=N'TestJob3', 
		@enabled=1
--...
--12 - 14 Rows of job Config info
-- ...
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_1', 
		@step_id=1, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_2]    Script Date: 4/5/2022 12:31:54 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_2', 
		@step_id=2, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_3]    Script Date: 4/5/2022 12:31:54 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_3', 
		@step_id=3, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_4]    Script Date: 4/5/2022 12:31:54 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_4', 
		@step_id=4, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_5]    Script Date: 4/5/2022 12:31:54 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_5', 
		@step_id=5, 
--...
--12 - 14 Rows of job step info
-- ...





TestJob3GO
/****** Object:  Job [TestJob4]    Script Date: 4/5/2022 12:32:14 PM ******/

EXEC msdb.dbo.sp_add_job @job_name=N'TestJob4', 
		@enabled=1
--...
--12 - 14 Rows of job Config info
-- ...
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_1', 
		@step_id=1, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_2]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_2', 
		@step_id=2, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_3]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_3', 
		@step_id=3, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_4]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_4', 
		@step_id=4, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_5]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_5', 
		@step_id=5, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_6]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_6', 
		@step_id=6, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_7]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_7', 
		@step_id=7, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_8]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_8', 
		@step_id=8, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_9]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_9', 
		@step_id=9, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_10]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_10', 
		@step_id=10, 
--...
--12 - 14 Rows of job step info
-- ...



/****** Object:  Step [TestJobStep_100]    Script Date: 4/5/2022 12:32:18 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_100', 
		@step_id=100, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_101]    Script Date: 4/5/2022 12:32:18 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestJobStep_101', 
		@step_id=101, 
--...
--12 - 14 Rows of job step info
-- ...


TestJob4GO

---

Then, I use a second regex S/R which will change any @job_id=@jobId string with the string @job_name=N'JOB' where JOB represents the corresponding Job_Name, in one go, only !

In addition it will delete the temporary Job_Name added in front of any GO string

• Open the Replace dialog ( Ctrl + H )

• SEARCH (?-is)(?<=\QEXEC msdb.dbo.sp_add_jobstep @job_\E)id=@jobId(?=(?s:.+?)^(.+)GO\R)|^(.+)GO\R

• REPLACE ?2GO\r\n:name=N'\1'

• Untick all box options

• Select the Regular expression search mode

• Click once only on the Replace All button

=> In the next post, You’ll see the expected OUTPUT text :

Best Regards,

guy038

[guy038]

Hello, @incrypt-_, @peterjones, @neil-schipper and *All,

And here is the expected result :

----------- Begin Job Step Name testing --------------



/****** Object:  Job [TestJob1]    Script Date: 4/5/2022 12:31:53 PM ******/

EXEC msdb.dbo.sp_add_job @job_name=N'TestJob1', 
		@enabled=0
--...
--12 - 14 Rows of job Config info
-- ...
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob1', @step_name=N'TestJobStep_1', 
		@step_id=1, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_2]    Script Date: 4/5/2022 12:31:53 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob1', @step_name=N'TestJobStep_2', 
		@step_id=2, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_3]    Script Date: 4/5/2022 12:31:53 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob1', @step_name=N'TestJobStep_3', 
		@step_id=3, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_4]    Script Date: 4/5/2022 12:31:53 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob1', @step_name=N'TestJobStep_4', 
		@step_id=4, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_5]    Script Date: 4/5/2022 12:31:53 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob1', @step_name=N'TestJobStep_5', 
		@step_id=5, 
--...
--12 - 14 Rows of job step info
-- ...





GO

/****** Object:  Job [TestJob2]    Script Date: 4/5/2022 12:31:54 PM ******/

EXEC msdb.dbo.sp_add_job @job_name=N'TestJob2', 
		@enabled=1
--...
--12 - 14 Rows of job Config info
-- ...
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob2', @step_name=N'TestJobStep_1', 
		@step_id=1, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_2]    Script Date: 4/5/2022 12:31:54 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob2', @step_name=N'TestJobStep_2', 
		@step_id=2, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_3]    Script Date: 4/5/2022 12:31:54 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob2', @step_name=N'TestJobStep_3', 
		@step_id=3, 
--...
--12 - 14 Rows of job step info
-- ...





GO

/****** Object:  Job [TestJob3]    Script Date: 4/5/2022 12:31:54 PM ******/

EXEC msdb.dbo.sp_add_job @job_name=N'TestJob3', 
		@enabled=1
--...
--12 - 14 Rows of job Config info
-- ...
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob3', @step_name=N'TestJobStep_1', 
		@step_id=1, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_2]    Script Date: 4/5/2022 12:31:54 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob3', @step_name=N'TestJobStep_2', 
		@step_id=2, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_3]    Script Date: 4/5/2022 12:31:54 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob3', @step_name=N'TestJobStep_3', 
		@step_id=3, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_4]    Script Date: 4/5/2022 12:31:54 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob3', @step_name=N'TestJobStep_4', 
		@step_id=4, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_5]    Script Date: 4/5/2022 12:31:54 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob3', @step_name=N'TestJobStep_5', 
		@step_id=5, 
--...
--12 - 14 Rows of job step info
-- ...





GO
/****** Object:  Job [TestJob4]    Script Date: 4/5/2022 12:32:14 PM ******/

EXEC msdb.dbo.sp_add_job @job_name=N'TestJob4', 
		@enabled=1
--...
--12 - 14 Rows of job Config info
-- ...
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob4', @step_name=N'TestJobStep_1', 
		@step_id=1, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_2]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob4', @step_name=N'TestJobStep_2', 
		@step_id=2, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_3]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob4', @step_name=N'TestJobStep_3', 
		@step_id=3, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_4]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob4', @step_name=N'TestJobStep_4', 
		@step_id=4, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_5]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob4', @step_name=N'TestJobStep_5', 
		@step_id=5, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_6]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob4', @step_name=N'TestJobStep_6', 
		@step_id=6, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_7]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob4', @step_name=N'TestJobStep_7', 
		@step_id=7, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_8]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob4', @step_name=N'TestJobStep_8', 
		@step_id=8, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_9]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob4', @step_name=N'TestJobStep_9', 
		@step_id=9, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_10]    Script Date: 4/5/2022 12:32:15 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob4', @step_name=N'TestJobStep_10', 
		@step_id=10, 
--...
--12 - 14 Rows of job step info
-- ...



/****** Object:  Step [TestJobStep_100]    Script Date: 4/5/2022 12:32:18 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob4', @step_name=N'TestJobStep_100', 
		@step_id=100, 
--...
--12 - 14 Rows of job step info
-- ...


/****** Object:  Step [TestJobStep_101]    Script Date: 4/5/2022 12:32:18 PM ******/
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob4', @step_name=N'TestJobStep_101', 
		@step_id=101, 
--...
--12 - 14 Rows of job step info
-- ...


GO

BR

guy038

[PeterJones]

@peterjones said in REGEX: How can I get cursor like behavior from a regex expression? (Somewhat working needs help):

a job for programming, not for regex

Based on the answers that Neil and Guy provided, I guess I misunderstood the original question – I thought the original problem had the replacement changing each time through… sorry for the bad advice.

(Still, if it were my data that I was trying to manipulate, I think writing a script would have been faster for me than trying to figure out a regex that was safe to run multiple times to get the same result. But convincing someone else to write the regex for you is much less effort, I will agree.)

[InCrypt _]

Peter,
The guys got the gist of my question for sure. Though I would have jumped at the chance to use a python snap in Notepad++, I’m in a locked-down environment, and though I’m provided Notepad++ and SSMS as tools to use, such snap-in’s need to be pre-authorized before they are permitted, and that is a long an onerous process, I’m sorry to say. (You have no idea what I had to go through just to get the COMPARE snap in loaded.)

As for getting regex help, I may have a brown belt in regex, or maybe not even that, but when I hit what looked like a regex bug, I knew I needed the help of the masters. And boy, did the masters step up!

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Niel, Guy,
Thank you both for your responses. When I verified them, both methods worked on my test file. So both answers are valid for the question as I posed it.

Niel, Your answer was more intuitive to me. Most likely because it was closest to what I already understood. But in the end, I decided to go with Guys’ method, even though I still don’t quite understand the regrex phrases and logic flow. (It’s still like magic to me.)

Both of you had used the “GO” statement as an “End of Job” flag for the regex expression. And though valid for the test file I had created, there was an edge case it ran afoul of; In the code that I had sanitized out of the test file, I had removed the JOB command syntax – the Stuff that SQL actually executes when the job is run. Unfortunately, in that syntax a couple of job steps had legal “GO” statements as part of the job command. Which of course broke things.

My solution was to add my own “---- END OF JOB” marker, and then use Guy’s method to replace the Job_ID’s with Job_Names. That was for two reasons, 1) it didn’t’ require the creation and running of a macro (Thus remaining inside the paradigm of regex expressions only). And 2) It seemed that guys approach was less prone to potential corruption, as it was a run once. Not a run macro over a hundred times).

Again, thank you both for your contributions. I learned a lot more about regex from this exorcise, getting me just that much closer to my Regex-Black-Belt. 😉

Oh, and to give back to the community.
This is the regex algorithm I developed to try and convert SQL jobs that you use the native job scripting tools within SSMS to generate, and convert that SQL Job script file from one that makes Local SQL Server jobs, to one that makes Multi-Server SQL jobs, which in turn can be run on the master server who will publish down to the target servers. It’s a “Use at your own risk” type algorithm, but for anyone finding themselves in a similar situation to mine, it may help you navigate the conversion just that much more easy….
— InCyrpt

--================================
--- Generte a single script of all SQL jobs you want to port over from a LOCAL server job set up
--- over to a multi-server setup. I did this using the Object Explorer method described in this 
--- article http://www.sqlcircuit.com/2013/08/how-to-script-all-sql-agent-jobs-in.html
--================================
-- Creating delete script
--  I prefer to mae a seperate drop and create set of scripts. 
--  So for this regex, all the drop job statements from the source script into their own script
--   Then run:
SEARCH:  (^.+?Object:\h+job\h+\[)(.+?)(\].+?\r\n.+?@job_)ID=N'.+?'(,.+?$)
REPLACE: \1\2\3Name=N'\2'\4
--================================
--- Mkaing the migration script. 
--  First, copy all the CREATE JOB statements into thier own script. 
--- The following set of regex experssions are predicated on ther eonly being ADD_JOB and UPDATE_job statements
--  in the script.
--  The following regex expressions are then used, in sequence, to massage the script from a single-server
--  to a multi-server script
--================================
-- Pass 1 - Regex for changing Job owner
SEARCH:  (^.+?@owner_login_name=N')(.+?)('.+?$)
REPLACE: \1<YourDomainSQLUser>\3
--================================
--- Pass 2 - Configuring job for Multi-Server operator 
--- (MSXOperator is the ONLY operator that can be used. https://www.sqlshack.com/multiserver-administration-master-target-sql-agent-jobs/)
SEARCH:  (@notify_email_operator_name=N'|@notify_page_operator_name=N')(.+?)('.*?$)
REPLACE: \1MSXOperator\3
--================================'
--- Pass 3 - Stripping out the IF statements
--- SPECIAL NOTE1: Run this replace as "Normal" and NOT a REGEX search.
SEARCH:  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
REPLACE: *** Replace With = Empty String ***
--================================
--- Pass 4 - Stripping out the @ReturnCode
SEARCH:  ^EXEC\h+@ReturnCode\h+=\h+msdb
REPLACE: EXEC msdb
--================================
--- Pass 5 - Setting the Job type
SEARCH:  (@category_name=N')(.+?)('.*?)$
REPLACE: \1[Uncategorized \(Multi-Server\)]\3
--================================
----- Stripping out the Transaction logic:
--- Pass 6a - Strpping out the headder
---    After running this statement, manually cut-n-paste 
---    the first occurance  of "---- END_OF_JOB" to the end of the last job in the script.
SEARCH:  (?s)(^/\*\*\*\*\*\*\h+Object:\h+Job\h+.+?$)(.+?)(EXEC\h+msdb.dbo.sp_add_job\h+@job_name.+?$)
REPLACE: ---- END_OF_JOB\r\n\1\r\n\3	

--- Pass 6b - Stripping out the footer
SEARCH:  (?s)^EXEC msdb.dbo.sp_update_job.+?EndSave:$
REPLACE: *** Replace With = Empty String ***
--================================
--- Pass 7 - Cleaning up servers settings
--- NOTE - Run as normal search.
SEARCH:  , @job_id = @jobId OUTPUT
REPLACE: *** Replace With = Empty String ***
--================================
--- Pass 8 - replacing job_id with job_name
-- Part 1
SEARCH:  (?s-i)\QEXEC msdb.dbo.sp_add_job @job_name=N'\E(.+?)'.+?\K---- END_OF_JOB
REPLACE: \1---- END_OF_JOB

--- part 2
SEARCH:  (?-is)(?<=\QEXEC msdb.dbo.sp_add_jobstep @job_\E)id=@jobId(?=(?s:.+?)^(.+)---- END_OF_JOB\R)|^(.+)---- END_OF_JOB\R
REPLACE: ?2---- END_OF_JOB\r\n:name=N'\1'
--================================
--- Pass 9 - adding in the Multi-Server element to the end of the job creating script.
SEARCH:  (?s)(EXEC msdb.dbo.sp_add_job\h+@job_Name=N')(.+?)('.+?)(?=---- END_OF_JOB)
REPLACE: \1\2\3EXEC msdb.dbo.sp_add_jobserver @job_name=N'\2', @server_name = N'TestServer_1'\r\nGO\r\nEXEC msdb.dbo.sp_add_jobserver @job_name=N'\2', @server_name = N'TestServer_2'\r\nGO\r\nEXEC msdb.dbo.sp_add_jobserver @job_name=N'\2', @server_name = N'TestServer_3'\r\nGO\r\nPRINT '----- Ending of job \2'\r\n
--================================

--================================
--- Adding the new Is Active Node testing step to each job.
--- on the master job server Run the script SEI_PUB_sp_AddAGPrimaryCheckStepToAgentJob.sql
--- Get either the delete or create script, and use the folloiwng regex to do a "Find all in doc"
SEARCH:  (^/\*\*\*\*\*\*\h+Object:\h+Job\h+.+?$)
--- Copy the capured results into a new text file.
--- use the following regex to generate the EXEC calls to sp_AddAGPrimaryCheckStepToAgentJob
SEARCH:  (.+?^/\*\*\*\*\*\*\h+Object:\h+Job\h+\[)(.+?)(\].+?$)
REPLACE: EXEC  sp_AddAGPrimaryCheckStepToAgentJob '\2'\r\nGO\r\n
--================================

[guy038]

Hello @Incrypt-_,

Would you like some information regarding the two regex S/R that I provided to you ?

BR

guy038

[Neil Schipper]

(Hi @guy038, I just finished typing this up. Feel free to critique or provide a nuts and bolts follow-up as you may wish.)

Hi @incrypt-_,

Nice to hear back from you, and that you were able to use a provided solution, and especially that you were able to make the necessary modification to handle details we were not aware of. (It’s not uncommon for people to come back with “It doesn’t work…”)

… Guys’ method, even though I still don’t quite understand the regrex phrases and logic flow. (It’s still like magic to me.)

I’ll try to explain.

To reduce wordiness, I’ll use InAccord to represent “in accordance with the match requirement appropriate to the context”

So, the challenge was to pick up a text1 InAccord, and plonk it down in place of some number, not known in advance, of other texts InAccord.

Guy’s solution has a simple first phase that picks up text1 and plonks down a copy of it at a safe location near the end of the whole group (block, record), ie, past all the locations where we wish to make replacements with that text1.

Now a second phase runs which tries to match a to-replace piece InAccord, skip past some in-between text, and then match and capture the text1 copy (also InAccord) parked near the end. Upon a full match, the regex plonks down text1 (with some fixed text) that achieves the desired substitution.

The magic is that only the to-replace piece is consumed by the regex scanner. The other texts are matched and captured from within a “lookahead” directive so they’re visited but not consumed. This enables the regex, after doing that little match & replace job, to continue its hunt from just past the freshly written text, not from the end of the record (even though the end of the record was visited).

There’s also another bit of magic where each failure to match a to-replace text causes (using regex alternate) a check for a match of record end, and if found, causes (using regex conditional capture group existence test) a clean out of the temporary text1.

[guy038]

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