find a line based on string pattern and move it X places

Question

I want to move the 1st instance of AND F.col_x IS NOT NULL pattern ( for each of the blocks 23 24 25 etc ) that follows the WHERE F.col_x = D.col_x pattern either of these ways
--Look for brackets Just before group by and add it in there
--alternately move that line 1 line below from where it was taken.
Either way the results would be the same

INPUT

*22 
     Select   ((MYLILFUNC(F.col_x,-99999)))   AS WIDTH,
              COUNT(*) AS  SIZE 
    FROM    MYDB.BGSQLTB F      where NOT EXISTS ( sel '1' from  MYDB.col_x D 
        WHERE F.col_x = D.col_x 
        AND F.col_x IS NOT NULL
        AND D.col_x IS NOT NULL  )  
    GROUP BY   F.col_x;
    *23 
     Select   ((MYLILFUNC(F.COL_y,-99999)))   AS WIDTH,
              COUNT(*) AS  SIZE 
    FROM    MYDB.BGSQLTB F      where NOT EXISTS ( sel '1' from  MYDB.DIM_DRG_CODE D 
        WHERE F.COL_y = D.COL_y 
    AND F.COL_y IS NOT NULL 
        AND D.COL_y IS NOT NULL  ) 
    GROUP BY   F.COL_y;
    *24 
     Select   ((MYLILFUNC(F.COL_Z,-99999)))   AS WIDTH,
              COUNT(*) AS  SIZE 
    FROM    MYDB.BGSQLTB F      where NOT EXISTS ( sel '1' from  MYDB.COL_Z D 
        WHERE F.COL_Z = D.COL_Z 
    AND F.COL_Z IS NOT NULL 
        AND D.COL_Z IS NOT NULL  ) 
    GROUP BY   F.COL_Z;
    *25 
     Select   ((MYLILFUNC(F.COL_XXX,-99999)))   AS WIDTH,
              COUNT(*) AS  SIZE 
    FROM    MYDB.BGSQLTB F      where NOT EXISTS ( sel '1' from  MYDB.COL_XX D 
        WHERE F.COL_XXX = D.COL_XXX 
    AND F.COL_XXX IS NOT NULL 
        AND D.COL_XXX IS NOT NULL  ) 
    GROUP BY   F.COL_XXX;

OUTPUT

*22 
 Select   ((MYLILFUNC(F.col_x,-99999)))   AS WIDTH,
          COUNT(*) AS  SIZE 
FROM    MYDB.BGSQLTB F      where NOT EXISTS ( sel '1' from  MYDB.col_x D 
    WHERE F.col_x = D.col_x 

    AND D.col_x IS NOT NULL  ) AND F.col_x IS NOT NULL 
GROUP BY   F.col_x;
*23 
 Select   ((MYLILFUNC(F.COL_y,-99999)))   AS WIDTH,
          COUNT(*) AS  SIZE 
FROM    MYDB.BGSQLTB F      where NOT EXISTS ( sel '1' from  MYDB.DIM_DRG_CODE D 
    WHERE F.COL_y = D.COL_y 

    AND D.COL_y IS NOT NULL  ) AND F.COL_y IS NOT NULL 
GROUP BY   F.COL_y;
*24 
 Select   ((MYLILFUNC(F.COL_Z,-99999)))   AS WIDTH,
          COUNT(*) AS  SIZE 
FROM    MYDB.BGSQLTB F      where NOT EXISTS ( sel '1' from  MYDB.COL_Z D 
    WHERE F.COL_Z = D.COL_Z 

    AND D.COL_Z IS NOT NULL  ) AND F.COL_Z IS NOT NULL 
GROUP BY   F.COL_Z;
*25 
 Select   ((MYLILFUNC(F.COL_XXX,-99999)))   AS WIDTH,
          COUNT(*) AS  SIZE 
FROM    MYDB.BGSQLTB F      where NOT EXISTS ( sel '1' from  MYDB.COL_XX D 
    WHERE F.COL_XXX = D.COL_XXX 

    AND D.COL_XXX IS NOT NULL  ) AND F.COL_XXX IS NOT NULL
GROUP BY   F.COL_XXX;

My search pattern using Ed is a bit too wide and takes more lines and I am not sure how I can get the moving logic done because it is relative to each selected line.

Kurt Stutsman · Accepted Answer · 2016-03-08T14:47:18.343

2

You can do this in a couple ways. With sed you can do

sed -e '/AND F\.[a-zA-Z_]* *IS *NOT *NULL/ { h; d }; /GROUP BY/ { H; x }'

What happens is anytime the first regular expression matches, the { h; d }; commands store the line in the hold buffer and move to the next line without outputting anything. Whenever the second regexp matches, the { H; x } append the current line to the hold buffer with a newline in between and then swap the hold buffer and the current line buffer. Then sed will automatically print out the pattern line. It's easy for this not to work correctly depending on your input, but it works fine for the sample you provided.

In awk it would be

awk 'tolower($0) ~ /and f.col_[a-z]* is not null/ {save = $0; next} /GROUP BY/ { print save } {print}'

edited Mar 08 '16 at 14:47

answered Mar 08 '16 at 02:46

Kurt Stutsman

3,994
17
23

I do not have GNU `sed` and `awk` just the POSIX plain BSD types. I hope it works with them – user1874594 Mar 08 '16 at 02:53
That last `n` doesn't do anything, does it? @user1874594 Do make the sed command work under BSD sed, you probably have to add a semicolon before the closing braces. – Benjamin W. Mar 08 '16 at 03:44
@BenjaminW. You're right. The `n` isn't needed. It looks like the `/I` suffix doesn't work in POSIX mode either. – Kurt Stutsman Mar 08 '16 at 03:46
I have updated the sed command to something that works in POSIX mode too. – Kurt Stutsman Mar 08 '16 at 04:04
Check out [this answer](http://stackoverflow.com/questions/24275070/sed-not-giving-me-correct-substitute-operation-for-newline-with-mac-difference/24276470#24276470) for everything about GNU and BSD sed differences, specifically the paragraph for "function lists". Unfortunately, the [POSIX sed standard](http://pubs.opengroup.org/onlinepubs/9699919799/utilities/sed.html) is not crystal clear about semicolons for one-liners ("permitted, but not required"), and BSD and GNU sed chose to implement them differently (BSD sed requires `{h;d;}`, where GNU sed is okay with `{h;d}`). – Benjamin W. Mar 08 '16 at 04:30
TY for helping but the SED does not work. Maybe if you can tell me a bit more about whats happpening inside I can get it to work. 1st there is the matching part I'd like to say Find me `AND followed by any spaces followed by F. ( think the dot is to escaped right ? ) Something that is full caps followed by any spaces followed by IS followed by any spaces followed by NOT followed by any spaces followed by NULL` then let sed verify if can pick up the regex followed by the substitute part. I dont understand the substitute part the ` h; d }; /GROUP BY/ { H; x }'` – user1874594 Mar 08 '16 at 08:35
I have updated the `sed` command to allow arbitrary space in between. If your problem gets any more complicated, you're probably going to want to use a real language like `perl` or `python` or at least use `awk`. – Kurt Stutsman Mar 08 '16 at 14:48
THanks. I am open to `awk` . Tried both and applied the modifications for `sed` to the `awk` ( no luck as yet )..but more than this , I wanted to understand the logic and I much appreciate your explanation . I think, based on this , I should get it to work. I wasn't looking for spoon feeds and your explanation and approach answers. I am choosing ( already upped it earlier ) this. TY – user1874594 Mar 10 '16 at 09:44

find a line based on string pattern and move it X places

1 Answers1