Need help with Regex in SQL Server query editior

Question

I have a file with thousand lines of codes with index details like below:

IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WITH (NOLOCK) WHERE name = 'IX_Test1' AND [object_id] = OBJECT_ID(N'Test1', N'U')) 
BEGIN 
 CREATE UNIQUE INDEX [IX_Test1] ON [Test1] 
( 
[cd] ASC 
) 
 WITH (FILLFACTOR = 90, IGNORE_DUP_KEY = OFF, PAD_INDEX = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF ) ON [PRIMARY] 
END 
IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WITH (NOLOCK) WHERE name = 'IX_Test2' AND [object_id] = OBJECT_ID(N'Test2', N'U')) 
BEGIN 
 CREATE INDEX [IX_Test2] ON [Test2] 
( 
[AdID] ASC , [Date] ASC 
) INCLUDE ( [AID], [LM] ) 
 WITH (FILLFACTOR = 90, IGNORE_DUP_KEY = OFF, PAD_INDEX = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF ) ON [PRIMARY] 
END

I want to format this data like below for comparison purposes:

IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WITH (NOLOCK) WHERE name = 'IX_Test1' AND [object_id] = OBJECT_ID(N'Test1', N'U')) BEGIN CREATE UNIQUE INDEX [IX_Test1] ON [Test1] ( [cd] ASC ) WITH (FILLFACTOR = 90, IGNORE_DUP_KEY = OFF, PAD_INDEX = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF ) ON [PRIMARY] END 
IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WITH (NOLOCK) WHERE name = 'IX_Test2' AND [object_id] = OBJECT_ID(N'Test2', N'U')) BEGIN CREATE INDEX [IX_Test2] ON [Test2] ( [AdID] ASC , [Date] ASC ) INCLUDE ( [AID], [LM] ) WITH (FILLFACTOR = 90, IGNORE_DUP_KEY = OFF, PAD_INDEX = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF ) ON [PRIMARY] END

So basically everything between IF and END should come as one line. I tried to do it using REGEX but didn't get it work. If someone could help me in this regard, it would be of great help.

Please let me know if you need any further details.

Answer 1

If you are willing to do this in two step, here is an approach that works, using the Regex option in SSMS's Replace dialog.

Step 1

Delete all line endings, squashing it all into a single line.

Replace: \r\n

With:

Explanation: The \r\n looks for a line ending, which is almost certainly a Carriage Return (\r) followed by a New Line (\n). Replace it with nothing.

Step 2

Replace all occurrences of "END" with "END" plus a new line ending.

Replace: END

With: END\r\n

Explanation: Replace "END" with "END" plus a carriage return plus a new line.

Oddity

Your example above has two spaces after the first "END", and no spaces after the second one. This will cause the results after Step 2 to look something like this:

IF NOT EXISTS...
 IF NOT EXISTS...

If your real code also has that oddity, you can fix that by ensuring that you don't have spaces after "END", with a Step 0 of:

Replace: END (two spaces after "END")

With: END (no spaces after "END")

Tested in SSMS 17.9.1.

• SQLPRODDBA: Thank you for your help! It saved lot of my hours to do manual comparision. Much appreciated!