Remove and replace between two specific strings using sed command

Question

Hi I have a SQL file i wanted to remove case statements which are present in a single line as well as case statements which are present in multiple lines. For example, my input file is:

Select a,
b,
c,
CASE when e in (1,0,2) then Y END as e
f,
g,
h,
CASE when i in (5,
6,
7,
9.
,10) then N
END as i
FROM ABCD

In my output it should be

Select a,
b,
c,
e as e
f,
g,
h,
i as i
FROM ABCD

I'm using the sed command

sed -i 's/\bCASE\b.*\bEND\b/${FIELD}/' $FILE

but this is working only for case and end statement which are on single line I want to make it work for case and end statements which are multiple lines too.

Answer 1

sed '/[Cc][aA][sS][eE] [wW][Hh][Ee][Nn]/{
      :1
      /[eE][nN][dD] [aA][sS]/!{
        N;b1
      }
      s/[Cc][aA][sS][eE] [wW][Hh][Ee][Nn] \([^ ]*\).*[eE][nN][dD]\( [aA][sS]\)/\1\2/
    }'

With GNU sed (which you seems to be using), that can be simplified to:

sed -E '/case when/I{
          :1
          /end as/I!{
            N;b1
          }
          s/case when ([^ ]*).*end( as)/\1\2/I
         }'

(that assumes no more than one case statement on a single line).

It would be a lot easier with perl:

perl: perl -0777 -pe 's/case when (\w+) in.*?end as /$1 as /gis'

(note that that command slurps the whole input in memory which could be a concern for huge files).