REGEX - How to capture blocks from CASE WHEN ... END

3

Good morning, guys.

I'm trying to make a regex to capture blocks from CASE WHEN ... END. So, from this string:

iduser + CaSe  WhEn ("end") = 0 THEN 'CASE WHEN' ELSE '  END ' END + sum(iduser ) + CASE  WHEN LANGUAGE = 3 THEN 4 ELSE 5 END

The blocks are captured:

CaSe  WhEn ("end") = 0 THEN 'CASE WHEN' ELSE 'END' END
CASE  WHEN LANGUAGE = 3 THEN 4 ELSE 5 END

What I get so far, was the result of a regex that I had done with a lot of sweat to get string in brackets, it looks like this:

(CASE\s*WHEN)([^)]+)(END)

However, there are some problems:

  • ([^)]+) - > This part does not make sense to me, but without it, it works;
  • I do not want regex to capture blocks in quotation marks (simple or doubles).
  • ([^)]+) - > In addition to making no sense, it causes string with parentheses to not work.

EDIT

The regex is like this now, I was able to evolve a little:

(?i)(CASE\s*WHEN)(\s*.*)(END)

Now it no longer has the meaningless parentheses and is case insensitive. But, you're still not ignoring the quotes. And that modification has stopped picking up all the blocks and is putting them all together in one.

Thank you in advance!

    
asked by anonymous 18.07.2017 / 13:53

1 answer

0

The main problem is the "end" because it would be the end delimiter. And since you have "end" in the middle of string it is not possible to do simply

(?i)(CASE\s+WHEN.*?END)

Because the catch is incorrect .

For this we have to analyze what this "end" of the medium has of different from the end, which is simple the quote.

Final solution

(?i)(CASE\s+WHEN.*?(?<!['"])END)

Explanation

  • (?i) - makes search case insentitive.
  • CASE\s+WHEN fetches the CASE WHEN statement with at least one space.
  • .*? - captures everything but tries as little as possible. Non-greedy operator
  • (?<!['"]) - is a negative-lookahead that checks the character before END , and this can not be double or double quotes.
  • END - Literal Search by END .

See it working

    
19.07.2017 / 21:20