CODEWITHSUNDEEP
regexnotepad++regex-group
Oo0Bailey0oO
Oo0Bailey0oO

Reputation: 11

How to combine multiple RegEx commands for Notepad++ using capture groups and alternations?

I am converting exported SQL views as files to a different syntax using a separate specialized conversion tool. This tool can't handle certain commands and formatting so I'm using Notepad++ with RegEx to alter the files ahead of time.

So far I am getting the results that I want, but it takes three separate Find/Replace actions. I'd like to reduce these three RegEx actions down to one if possible.

Find: (.*)(CREATE VIEW.*\nGO)(.*)
Replace: \2

Find: (CREATE VIEW )(.*)(\r\nAS)
Replace: \1"\2"\3

Find: (oldschema1\.|\[oldschema1\]\.|\[|\]|TOP \(100\) PERCENT|oldschema2\.)|(^GO$)|(\A^(.*?))
Replace: (?1)(?2\;)(?3SET SCHEMA schemaname\; \n\n\1)```

I'm using Notepad++ 7.7.1 64-bit, Find/Replace with Regular Expression search mode - ". matches newline" check on.

You'll see in my code that I'm already using capture groups with alternation. I thought I could combine the first two RegEx steps as additional capture groups to Step 3 but it doesn't work out, possibly because they are nested.

I tried referencing the nested groups by incrementing the referencing number accordingly, but it doesn't work (blanks out the result).

Here is an example SQL view file. It's not a working view because I added "oldschema2" so the RegEx would have something to find for one of the replacements, but it's representative as an example here.

garbage 
text 
beforehand

CREATE VIEW [oldschema1].[viewname]
AS
SELECT DISTINCT 
                         TOP (100) PERCENT oldschema1.TABLENAME.FIELD1, oldschema1.TABLENAME.FIELD2
FROM            oldschema1.TABLENAME
WHERE        (oldschema1.TABLENAME.FIELD3 = N'Z003') AND oldschema2.TABLENAME.FIELD2 = 1
ORDER BY oldschema1.TABLENAME.FIELD1

GO

garbage 
text
after

Here is some additional details of what I'm trying to achieve with each pass.

Notepad++ RegEx Step 1 - isolate view block from CREATE VIEW to GO

Find: (.*)(CREATE VIEW.*\nGO)(.*)

Replace: \2

Step 2 - put quotes around view name

Find: (CREATE VIEW )(.*)(\r\nAS)

Replace: \1"\2"\3

Step 3 - remove/replace various texts and insert a line at the beginning of the file

Find: (oldschema1\.|\[oldschema1\]\.|\[|\]|TOP \(100\) PERCENT|oldschema2\.)|(^GO$)|(\A^(.*?))

Replace: (?1)(?2\;)(?3SET SCHEMA schemaname\; \n\n\1)

The expected output from the above example would be:

SET SCHEMA schemaname;

CREATE VIEW "viewname"
AS
SELECT DISTINCT 
                         TABLENAME.FIELD1, TABLENAME.FIELD2
FROM            TABLENAME
WHERE        (TABLENAME.FIELD3 = N'Z003') AND TABLENAME.FIELD2 = 1
ORDER BY TABLENAME.FIELD1

;

which I achieve with the above three steps, but I'd like to do it in one Find/Replace if possible.

I'm pretty new to RegEx, and StackOverflow for that matter. Your help is greatly appreciated.

Upvotes: 1

Views: 606

Answers (1)

Emma
Emma

Reputation: 27743

Step 1

I'm not so sure about it, but I'm guessing that maybe we would want an expression similar to:

[\s\S]*?(CREATE VIEW[\s\S]*GO\s*)[\s\S]*

to be replaced with $1, where our desired data is in this capturing group:

(CREATE VIEW[\s\S]*GO\s*)

and we can even remove \s*:

(CREATE VIEW[\s\S]*GO)

and just try:

[\s\S]*?(CREATE VIEW[\s\S]*GO)[\s\S]*

with an m flag.

In the right panel of this demo, the expression is further explained, if you might be interested.

Step 2

We can likely try:

(CREATE VIEW)(.*)

and replace with:

SET SCHEMA schemaname;\n\n$1 "viewname"

Demo

Step 3

This step would probably be done with an expression similar to:

TOP \(100\) PERCENT |oldschema1\.

being replaced with an empty string.

Demo

Step 4:

\s*GO being replaced with \n; or just ; and we might likely have the desired output, not sure though.

Demo

Upvotes: -1

Related Questions