1

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.

  • Would be easier with perl: perl -0777 -pe 's/case when (\w+) in.*?end as /$1 as /gis' – Stéphane Chazelas Apr 30 '15 at 11:01
  • perl wont be supported by our platform any other option using sed or awk – Priyanka Apr 30 '15 at 11:08
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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.