shell script for below logic

linuxred · 05-30-2011, 12:00 PM

Hi Team,

I need shell script for below logic. I have file

string1
string2
string3
ALTER TABLE TNAME
ADD COLUMN (C1 DATATYPE);
String4
alter table tname1
add column
(c1 number,
c2 char)
;
string 5

Now i need to extract ALTER statememnts...read from ALTER statement and end till ; is encountered.

ALTER statements are not fixed in length.

I need shell script to read the above file and extract alter commands till ;

please help

colucix · 05-30-2011, 12:05 PM

Code:

awk '/^ALTER/,/^;$/' file

Welcome to LinuxQuestions!

arizonagroovejet · 05-30-2011, 12:08 PM

This smells a lot like homework. How about you post what you have so far, and then maybe someone can tell you where you're going wrong.

linuxred · 05-30-2011, 12:24 PM

Quote:

Originally Posted by colucix

Code:

awk '/^ALTER/,/^;$/' file

Welcome to LinuxQuestions!

Thanks, but that does not seem to fit my purpose. The output of above command is

ALTER TABLE ADD COLUMN
(COL1)
;
STRING3
ALTER TABLE....
STRING 4

I am getting in between strings also. Only it has removed the strings above first ALTER. I want only ALTER commands no strings in between

colucix · 05-30-2011, 12:32 PM

Code:

awk '/^ALTER/{_=1}/^;$/{print;_=0}_'

Question: is there always a semi-colon (on its own line) to close the group of lines after ALTER? Or is there an ALTER toward the end of the file that is not closed by a semi-colon? Another question: can you understand the above code and try to elaborate it in order to match your requirements? In other words: what are your awk skills?

linuxred · 05-30-2011, 12:41 PM

Quote:

Originally Posted by colucix

Code:

awk '/^ALTER/{_=1}/^;$/{print;_=0}_'

Question: is there always a semi-colon (on its own line) to close the group of lines after ALTER? Or is there an ALTER toward the end of the file that is not closed by a semi-colon? Another question: can you understand the above code and try to elaborate it in order to match your requirements? In other words: what are your awk skills?

ALTER statement is always ended by ; ...I need to extract only ALTER statement.

I am not confortable with awk commands. The output did not change...

I am getting

ALTER TABLE......
....
;
string 3;
ALTER TABLE .....
...
;

colucix · 05-30-2011, 01:13 PM

Ok. Maybe it depends on how the ending semi-colon is placed. My code looks for a semi-colon without any other character on the same line, that is no spaces before and after. Try to change the regular expression to something more suitable to your input:

Code:

/^;$/

for example you may try something like:

Code:

/^;/  # semi-colon at the beginning of the line followed by anything
/;$/  # semi-colon at the end of the line
/;/   # semi-colon in the middle of other stuff (if any)

awk code explained:

Code:

/^ALTER/ {
  #
  #  We encounter the ALTER statement at the beginning of the line:
  #  set the flag _ to 1 (TRUE)
  #
  _ = 1
}

/^;$/ {
  #
  #   We encounter a semi-colon alone in its own line:
  #   print the semicolon and set the flag _ to 0 (FALSE)
  #
  print
  _ = 0
}

#
#  Depending on the value of the _ flag the line is printed or not
#
_

MTK358 · 05-30-2011, 01:23 PM

colucix: It's obvious from the original post that the semicolon is not on the same line. This should work:

Code:

awk 'BEGIN { RS="" } { gsub(/ALTER[^;]*;/, "", $0); print }'

TB0ne · 05-30-2011, 01:53 PM

Quote:

Originally Posted by linuxred

ALTER statement is always ended by ; ...I need to extract only ALTER statement.
I am not confortable with awk commands. The output did not change...I am getting

ALTER TABLE......
....
;
string 3;
ALTER TABLE .....
...
;

I agree with arizonagroovejet...why don't you post what you've written? Or are you wanting us to write and debug your scripts for you?

grail · 05-30-2011, 07:54 PM

Might also be nice if you posted the expected output seeing as the guessing game to get what you want is not working.

chrism01 · 05-30-2011, 11:22 PM

Seems to me those are (prob Oracle) SQL Alter Table ...; statements, with other strings in between (possibly comments).
He just wants the SQL out, presumably to run directly. Therefore, give the example in post #1, output would be

Code:

ALTER TABLE TNAME
ADD COLUMN (C1 DATATYPE);

alter table tname1
add column
(c1 number,
c2 char)
;

linuxred · 05-30-2011, 11:46 PM

Hi,

I will work with your inputs. Thanks for your time and information