Mdthbs

Why do sellers care about down payments?

How do I get rid of distorted pictures of distant objects photographed with a telephoto lens?

Extending a 2d plot to 3d

Leaving out pronouns in informal conversation

Splice or replace

Evidence that matrix multiplication cannot be done in O(n^2 poly(log(n))) time

Does an oscilloscope subtract voltages as phasors?

Confirm the ending of a string

Does my opponent need to prove his creature has morph?

In Germany, how can I maximize the impact of my charitable donations?

Resume: How to quantify my contributions as a software engineer?

How to say "quirky" in German without sounding derogatory?

Can I toggle Do Not Disturb on/off on my Mac as easily as I can on my iPhone?

Where can I get an anonymous Rav Kav card issued?

Make 1998 using the least possible digits 8

Writing a love interest for my hero

Do all humans have an identical nucleotide sequence for certain proteins, e.g haemoglobin?

Is English tonal for some words, like "permit"?

Will replacing a fake visa with a different fake visa cause me problems when applying for a legal study permit?

Newly created XFS filesystem shows 78GB used

Do they still use tiger roars in the 2019 "Lion King" movie?

Random point on a sphere

Where can I find vomiting people?

Fight a biblical flood apart from building barriers

Remove and replace between two specific strings using sed command

sed command to replace a blank line with two lines of contentsed syntax to remove 2 characters only when it is box and spacesed to search for multiple words and remove those linesSed alternative for search and replace on very long linesHow to Remove Strings between two Parenthesis in UnixRemove first and last character if presentsed script to remove all lines with a pattern and append lines at the endHow to use sed and regular expressions to find pattern and remove last few characters?Use sed to prefix and suffix multiple strings per line

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}

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/bCASEb.*bENDb/${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.

sed

share|improve this question

edited 4 mins ago

Philippos

7,36911 gold badge2121 silver badges5555 bronze badges

asked Apr 30 '15 at 10:34

PriyankaPriyanka

1633 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment
 | 

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/bCASEb.*bENDb/${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.

sed

share|improve this question

edited 4 mins ago

Philippos

7,36911 gold badge2121 silver badges5555 bronze badges

asked Apr 30 '15 at 10:34

PriyankaPriyanka

1633 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment
 | 

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/bCASEb.*bENDb/${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.

sed

share|improve this question

edited 4 mins ago

Philippos

7,36911 gold badge2121 silver badges5555 bronze badges

asked Apr 30 '15 at 10:34

PriyankaPriyanka

1633 bronze badges

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

Select a,

b,

c,

e as e

f,

g,

h,

i as i

FROM ABCD

sed -i 's/bCASEb.*bENDb/${FIELD}/' $FILE

share|improve this question

edited 4 mins ago

Philippos

7,36911 gold badge2121 silver badges5555 bronze badges

asked Apr 30 '15 at 10:34

PriyankaPriyanka

1633 bronze badges

share|improve this question

edited 4 mins ago

Philippos

7,36911 gold badge2121 silver badges5555 bronze badges

asked Apr 30 '15 at 10:34

PriyankaPriyanka

1633 bronze badges

edited 4 mins ago

Philippos

7,36911 gold badge2121 silver badges5555 bronze badges

edited 4 mins ago

Philippos

7,36911 gold badge2121 silver badges5555 bronze badges

asked Apr 30 '15 at 10:34

PriyankaPriyanka

1633 bronze badges

asked Apr 30 '15 at 10:34

PriyankaPriyanka

1633 bronze badges

1 Answer
1

active

oldest

votes

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])/12/

    }'

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)/12/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).

share|improve this answer

answered Apr 30 '15 at 11:28

Stéphane ChazelasStéphane Chazelas

334k5858 gold badges652652 silver badges10231023 bronze badges

add a comment
 | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "106"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/4.0/"u003ecc by-sa 4.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f199596%2fremove-and-replace-between-two-specific-strings-using-sed-command%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

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])/12/

    }'

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)/12/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).

share|improve this answer

answered Apr 30 '15 at 11:28

Stéphane ChazelasStéphane Chazelas

334k5858 gold badges652652 silver badges10231023 bronze badges

add a comment
 | 

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])/12/

    }'

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)/12/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).

share|improve this answer

answered Apr 30 '15 at 11:28

Stéphane ChazelasStéphane Chazelas

334k5858 gold badges652652 silver badges10231023 bronze badges

add a comment
 | 

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])/12/

    }'

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)/12/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).

share|improve this answer

answered Apr 30 '15 at 11:28

Stéphane ChazelasStéphane Chazelas

334k5858 gold badges652652 silver badges10231023 bronze badges

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])/12/

    }'

sed -E '/case when/I{

          :1

          /end as/I!{

            N;b1

          }

          s/case when ([^ ]*).*end( as)/12/I

         }'

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

share|improve this answer

answered Apr 30 '15 at 11:28

Stéphane ChazelasStéphane Chazelas

334k5858 gold badges652652 silver badges10231023 bronze badges

answered Apr 30 '15 at 11:28

Stéphane ChazelasStéphane Chazelas

334k5858 gold badges652652 silver badges10231023 bronze badges

answered Apr 30 '15 at 11:28

Stéphane ChazelasStéphane Chazelas

334k5858 gold badges652652 silver badges10231023 bronze badges