I need to remove line breaks between two keywords. That is "ELSE" and till the end of the statement ended by ",".

Sample text:

CAST (
    CASE
        WHEN flights3 = 0 THEN null
        ELSE ( XY_speedwind3 - ((X_wind3 * Y_speed3) / flights3) ) END AS FLOAT ) AS XY_speedwindb3,
CAST (
    CASE
        WHEN flights3 = 0 THEN null
        ELSE (
            XY_speedairtime3 - ((X_airtime3 * Y_speed3) / flights3)
        )
    END AS FLOAT
) AS XY_speedairtimeb3,
CAST (
    CASE
        WHEN flights3 = 0 THEN null
        ELSE ( XY_speeddistance3 - ((X_distance3 * Y_speed3) / flights3) ) END AS FLOAT
) AS XY_speeddistanceb3,

Expected output:

CAST (
    CASE
        WHEN flights3 = 0 THEN null
        ELSE ( XY_speedwind3 - ((X_wind3 * Y_speed3) / flights3) ) END AS FLOAT ) AS XY_speedwindb3,
CAST (
    CASE
        WHEN flights3 = 0 THEN null
        ELSE (XY_speedairtime3 - ((X_airtime3 * Y_speed3) / flights3)) END AS FLOAT ) AS XY_speedairtimeb3,
CAST (
    CASE
        WHEN flights3 = 0 THEN null
        ELSE ( XY_speeddistance3 - ((X_distance3 * Y_speed3) / flights3) ) END AS FLOAT ) AS XY_speeddistanceb3,

So basically the idea is to join all the lines between "ELSE" and the comma "," into a single line.

I have tried some approaches (e.g. ELSE. [\r\n] .*[,]) but not succeeded so far.

Do you have any ideas? Thanks.

CodePudding user response：

You can try with the following regex:

(?<=CASE|ELSE \(|\))\s (?=WHEN|\)|END AS)|\s (?=\) AS)

It will replace spaces \s with a space in two cases:

• (?<=CASE|ELSE \(|\))\s (?=WHEN|\)|END AS):
  • when they are preceeded by CASE or ELSE \( or \)
  • and followed by WHEN or \) or END AS
• (?=\) AS): when they are followed by \) AS

Check the demo

Screenshot (after):