REGEX: Global replace column name with instance of alias in SQL

wg992

Fellow Notepad++ Users,

Could you please help me the the following search-and-replace problem I am having?

I’m trying to clean some auto-generated SQL and I’ve gotten many of my S/R using REGEX to work. This one is a bit tricky. I’ve spent half a day browsing your community forum and tried various solutions. I’m not sure if what I want to do can be done.

Here is the data I currently have (“before” data):

SELECT
        D1.C0 AS ParentRoute, 
        D1.C1 AS Route_nk, 
        D1.C2 AS RouteDescription, 
        D1.C3 AS TruckDescription, 
        D1.C4 AS DriverName, 
        D1.C5 AS DepartTime, 
        D1.C6 AS MaxCube, 
        {...}
    FROM
        (
        SELECT
            D1.C0 AS C0, 
            D1.C1 AS C1, 
            D1.C2 AS C2, 
            D1.C3 AS C3, 
            D1.C4 AS C4, 
            D1.C5 AS C5, 
            D1.C6 AS C6, 
           {...}

Here is how I would like that data to look (“after” data):

SELECT
        D1.ParentRoute, 
        D1.Route_nk, 
        D1.RouteDescription, 
        D1.TruckDescription, 
        D1.DriverName, 
        D1.DepartTime, 
        D1.MaxCube, 
        {...}
    FROM
        (
        SELECT
              D1.ParentRoute, 
              D1.Route_nk, 
              D1.RouteDescription, 
              D1.TruckDescription, 
              D1.DriverName, 
              D1.DepartTime, 
              D1.MaxCube, 
           {...}

To accomplish this, I have tried various expressions using different flavors of the following Find/Replace expressions and settings

Find What = '(D1\.|\G)(C[0-9])\sAS\s(\w*)?\K\1'
Replace With = '$2'
Search Mode = REGULAR EXPRESSION
Dot Matches Newline = I have tried both

I’m not sure if globally replacing C[0-9] with the proper alias is possible.

Unfortunately, this did not produce the output I desired, and I’m not sure why. Could you please help me understand what went wrong and help me find the solution? I’m not sure I understand the \G and \K assertions.

PeterJones

@wg992,

Sorry for the lack of responses. I don’t have a full solution for you, which is why I didn’t reply immediately. But since no one else has chimed in, I will just offer some hints.

It might be possible to do it with one or more regexes – I know that in years past, @guy038 has offered various lookup-table–based replacement solutions. But searching through his gazillion-and-one regex posts in this forum might be difficult to find the one you want. I don’t think I have any of those lookup-table solutions bookmarked, unfortunately.

But if I were solving the problem, and wanted to be completely inside Notepad++, I would work up a script for the PythonScript plugin which would first convert the first group of D#.C# AS XXX into a lookup Dict structure of some sort, then go through and perform a search-and-replace using PythonScript syntax. (But really, I’d just use Notepad++ to write a program to do it in Perl syntax, which I’m more comfortable with than Python, and not bother with trying to have the file open in Notepad++ – I would just use Perl’s IO routines to read and output the file, and bypass the Notepad++ middle-man, which just makes the syntax more complicated.)

Neil Schipper

@wg992 I achieved the transformation of your sample data using a macro I recorded which carried out conventional editing, searching and copy/paste operations. The macro acted on a single pair of corresponding D1. rows. I then ran the macro as many times as needed.

Note that within a macro, all of these operations are fully supported: selecting text, launching a search on that text via Ctl-F3, search again with F3, and, fresh clipboard copy/paste operations.

Care is needed in regard to the cursor position before launching the macro, and, where the macro leaves the cursor.

There are caveats, and your actual files may contain features that prevent this approach from being a desirable “all in one go” solution.

For instance if a file contains a large number of separate outer level SELECT {} structures, using the simple macro I used (which worked on the first try) will be cumbersome in that you’ll have to manually position the cursor between batches of macro runs. However, a more devious macro which incorporates a regex search (for the 1st instance of D1\d\. AS following SELECT) could be devised to overcome that.