Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

sed - pattern match - apply substitution

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting sed - pattern match - apply substitution
# 1  
Old 09-12-2016
sed - pattern match - apply substitution
Greetings Experts,
I am on AIX and in process of creating a re-startable script that connects to Oracle and executes the statements. The sample contents of the file1 is
Code:
CREATE OR REPLACE VIEW DB_V.TAB1 AS SELECT * FROM DB_T.TAB1;
....
CREATE OR REPLACE VIEW DB_V.TAB10 AS SELECT * FROM DB_T.TAB10;

CREATE INDEX TAB1_COL1 ON TAB1 (COL1);
...
CREATE INDEX TAB10_COL1 ON TAB10 (COL1);
...

Create index statements are one-liner.
The script should be restartable and hence at the beginning of the script I have added a SPOOL to see what are all the indexes present for the respective tables and then comment them appropriately ( I don't want to remove them as I am modifying an existing template file).

For this, I am reading the spool file (which has already existing index names only) as

Code:
while read line
do
sed "/$line/ s/\(.*\)/--\1/g" < file1.txt > file1_temp.txt

rm file1.txt;
mv file1_temp.txt file1.txt

done < spool_file.txt;

In-line substitution sed -i is not supported on my system.
The sed statement is failing with Parsing error, where I could see $line is substituted correctly, the s// part being ignored. However, at command prompt, I have assigned line=TAB1_COL1 then executed the below
Code:
sed "/$line/ s/\(.*\)/--\1/g" < file1.txt

and could see that the existing indexes are being commented out as
--CREATE INDEX TAB1_COL1 ON TAB1 (COL1);
which can be fed to Oracle so that the script doesn't fail. I am not sure what is the error as it works on the command prompt and fails at the script level. Please note that the statement doesn't contain any embedded / or \ statements in case it matters. Thank you for your time.

EDIT:
Kindly note that I intend to do in
sed /pattern/ s// instead of sed s/.*pattern.*//
Last edited by chill3chee; 09-12-2016 at 01:48 PM.. Reason: Added the desired format
# 2  
Old 09-12-2016
Help me out - I can't see an s// anywhere in your script? Some more details of your input would help as well, wouldn't they?
# 3  
Old 09-12-2016
Hi RudiC/Ravinder Singh,
The file1 specified in the above is sql file generated after applying modifications to the template file. Now file1.txt had to be run in Oracle whose sample contents are
Code:
CREATE OR REPLACE VIEW DB_V.TAB1 AS SELECT * FROM DB_T.TAB1;
....
CREATE OR REPLACE VIEW DB_V.TAB10 AS SELECT * FROM DB_T.TAB10;
CREATE INDEX TAB1_COL1 ON TAB1 (COL1);
...
CREATE INDEX TAB6_COL1 ON TAB6 (COL1);
CREATE INDEX TAB7_COL1 ON TAB7 (COL1)
...
CREATE INDEX TAB10_COL1 ON TAB10 (COL1);

Say, if the CREATE INDEX TAB7_COL1 statement failed for some reason (space issue or others), after issue identification and fix applied, I just need to restart the existing file1 script, which should now be transformed to
Code:
CREATE OR REPLACE VIEW DB_V.TAB1 AS SELECT * FROM DB_T.TAB1;
....
CREATE OR REPLACE VIEW DB_V.TAB10 AS SELECT * FROM DB_T.TAB10;

--CREATE INDEX TAB1_COL1 ON TAB1 (COL1);  This is commented
...
--CREATE INDEX TAB6_COL1 ON TAB6 (COL1);  This is commented
CREATE INDEX TAB7_COL1 ON TAB7 (COL1);
...
CREATE INDEX TAB10_COL1 ON TAB10 (COL1);

The spool file contents spool_file.txt are
Code:
TAB1_COL1
TAB2_COL1
TAB3_COL1
TAB4_COL1
TAB5_COL1
TAB6_COL1

Here create index from TAB1_COL1 to TAB6_COL1 are commented out as they are already present in the spool file (spool file is generated by checking what indexes are already present) through below excerpt from script.
Code:
sqlplus -s ....
spool spool_file.txt;
select index_name from dba_indexes where index_name='TAB1_COL1'
union
select index_name from dba_indexes where index_name='TAB2_COL1'
union
...
select index_name from dba_indexes where index_name='TAB10_COL1';
spool off;
........
while read line
do
sed "/$line/ s/\(.*\)/--\1/g" < file1.txt > file1_temp.txt

rm file1.txt;
mv file1_temp.txt file1.txt

done < spool_file.txt;
....
@file1.txt ;

and then finally execute the file1.txt contents which should execute successfully. I am not able to resolve the parsing issue relative to the statement sed "/$line/ s/\(.*\)/--\1/g" < file1.txt , which runs good (-- comments out the respective line) when I verified by assigning a sample value to line variable.
# 4  
Old 09-12-2016
Hello chill3chee,

I am still not clear about your requirements, let's say variable line=TAB1_COL1, then following may help you in same.
Similarly you could use this following in your whileloop.
Code:
line=TAB1_COL1
sed '/'"$line"'/s/\(.*\)/--\1/'  Input_file

Output will be as follows.
Code:
spool spool_file.txt;
--select index_name from dba_indexes where index_name='TAB1_COL1'
union
select index_name from dba_indexes where index_name='TAB2_COL1'
union
...
select index_name from dba_indexes where index_name='TAB10_COL1';
spool off;

Also in your above code you shouldn't do < Input_fileto sed do as above code and redirect output to > file1_temp.txt. If you have any other requirements then please mention it more clearly with examples.
Please let me know if you have any queries on same.

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
chill3chee
# 5  
Old 09-12-2016
Please show your error messages, better: an execution log.
I don't get errors when executing
Code:
while read line;   do sed "/$line/ s/^/--/" file1;  done < spool_file.txt;

# 6  
Old 09-12-2016
Why don't you read file1 and spool_file just once each instead of file1 once per line in spool_file?
Code:
awk 'NR==FNR {T[$1]; next} {for (t in T) if ($0 ~ t) $0 = "--" $0} 1' file2 file1
CREATE OR REPLACE VIEW DB_V.TAB1 AS SELECT * FROM DB_T.TAB1;
....
CREATE OR REPLACE VIEW DB_V.TAB10 AS SELECT * FROM DB_T.TAB10;
--CREATE INDEX TAB1_COL1 ON TAB1 (COL1);
...
--CREATE INDEX TAB6_COL1 ON TAB6 (COL1);
CREATE INDEX TAB7_COL1 ON TAB7 (COL1)
...
CREATE INDEX TAB10_COL1 ON TAB10 (COL1);

This User Gave Thanks to RudiC For This Post:
chill3chee
# 7  
Old 09-12-2016
Quote:
Originally Posted by RudiC
Why don't you read file1 and spool_file just once each instead of file1 once per line in spool_file?
Code:
awk 'NR==FNR {T[$1]; next} {for (t in T) if ($0 ~ t) $0 = "--" $0} 1' file2 file1
CREATE OR REPLACE VIEW DB_V.TAB1 AS SELECT * FROM DB_T.TAB1;
....
CREATE OR REPLACE VIEW DB_V.TAB10 AS SELECT * FROM DB_T.TAB10;
--CREATE INDEX TAB1_COL1 ON TAB1 (COL1);
...
--CREATE INDEX TAB6_COL1 ON TAB6 (COL1);
CREATE INDEX TAB7_COL1 ON TAB7 (COL1)
...
CREATE INDEX TAB10_COL1 ON TAB10 (COL1);

I agree wholeheartedly with the idea of using a single invocation of awk instead of one invocation of sed for each line in a file. But, we don't have enough data to properly process the needed matches. If the matches are always on the 3rd field, to prevent something like TAB6_COL1 in the list of changes from also affecting lines containing TAB6_COL10 through TAB6_COL19, you might need something more like:
Code:
awk 'NR==FNR {T[$1]; next} {if ($3 in T) $0 = "--" $0} 1' file2 file1

or if the string could appear in another field:
Code:
awk 'NR==FNR {T[" " $1 " "]; next} {for (t in T) if ($0 ~ t) $0 = "--" $0} 1' file2 file1

and, if there is a chance that a line might already have been commented out and you don't want to add multiple sets of leading hyphens:
Code:
awk 'NR==FNR {T[$1]; next} !/^--/{if ($3 in T) $0 = "--" $0} 1' file2 file1

or:
Code:
awk 'NR==FNR {T[" " $1 " "]; next} !/^--/{for (t in T) if ($0 ~ t) $0 = "--" $0} 1' file2 file1

And, of course, further adjustments would be needed if the string to be matched could appear at the start of a line or at the end of a line.

And, if you were willing to use:
Code:
sed "s/.*pattern.*/--&/"

instead of:
Code:
sed '/pattern/s/^/--/'

and the patterns don't overlap, you could also create a file of sed commands like:
Code:
s/.*pattern1.*/--&/
s/.*pattern2.*/--&/
s/.*pattern3.*/--&/
...
s/.*patternn.*/--&/

and run sed once:
Code:
sed -f sed_commands_file input_file > output_file

but you still have to consider the possibility of matching substrings of longer unintentional matches.
This User Gave Thanks to Don Cragun For This Post:
chill3chee
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Get range out using sed or awk, only if given pattern match

Input: START OS:: UNIX Release: xxx Version: xxx END START OS:: LINUX Release: xxx Version: xxx END START OS:: Windows Release: xxx Version: xxx ENDHere i am trying to get all the information between START and END, only if i could match OS Type. I can get all the data between the... (3 Replies)
Discussion started by: Dharmaraja
3 Replies

2. Shell Programming and Scripting

Pattern match with awk/sed - help

I need to grep for the pattern text inside the square brackets which are in red and not in green..my current code greps patterns both of them, which i don't want Input fileref|XP_002371341.1| oxoacyl-ACP reductase, putative gb|EPT24759.1| 3-ketoacyl-(acyl-carrier-protein) reductase ... (2 Replies)
Discussion started by: selvankj
2 Replies

3. Shell Programming and Scripting

sed : match one pattern then the next consecutive second pattern not working

Ive used this snippet of code on a solaris box thousands of times. But it isnt working on the new linux box sed -n '/interface LoopBack0/{N;/ ip address /p;}' *.conf its driving me nuts !! Is there something Im missing ? (7 Replies)
Discussion started by: popeye
7 Replies

4. UNIX for Dummies Questions & Answers

sed multiline pattern match

How can I write a script that takes a cisco config file and outputs every occurrence of two, or more, pattern matches through the whole config file? For example, out of a config file, i want to print out every line with interface, description and ip address through the whole file, and disregard... (3 Replies)
Discussion started by: knownasthatguy
3 Replies

5. Shell Programming and Scripting

Sed Pattern Match

Hi, I would like to use SED to do the following string replacement: asd1abc to www1cda asd2abc to www2cda ... asd9abc to www9cda I can use 'asd.abc' to find the orignal string, however I don't know how to generate the target string. Any suggestion? Thanks, ... (2 Replies)
Discussion started by: mail4mz
2 Replies

6. Shell Programming and Scripting

Match a pattern starting with sub-pattern using sed

Hi all, I've been experiencing a difficulty trying to match a number and write it to a new file. My input file is: input.txt It contains the lines: 103P 123587.256971 3.21472112 3.1517423 1.05897234566427 58.2146258 12.35478 25.3612489 What would be the sed command to... (17 Replies)
Discussion started by: Biederman
17 Replies

7. Shell Programming and Scripting

sed pattern match problem

Hi all, hoping this is a simple one, tried looking but just can't see the solution As an example I've got a list of words that all start Ben..... Bendicks Benefiber Ben Benylin I need to only change the line Ben with Ben 10, ignoring the other lines. I tried the following ... (1 Reply)
Discussion started by: mrpugster
1 Replies

8. Shell Programming and Scripting

sed pattern substitution issue?

Hello everyone ... I'm going crazy, I hope some of you can help me ... I have to replace a line in a crontab like this: 5 2 * * 2 root backupdat with this: 5 5 * * 3 root backupdat the command I use is the following: sed -i.bak -e 's/5 2 * * 2 root backupdat/5 5 * * 3 root... (4 Replies)
Discussion started by: ionral
4 Replies

9. Shell Programming and Scripting

sed print all lines after pattern match

HiCan someone show me how to print all lines from a file after a line matching a pattern using sed?Thanks (13 Replies)
Discussion started by: steadyonabix
13 Replies

10. Shell Programming and Scripting

pattern match and substitution, can you help?

pattern match and substitution, can you help? file named test.txt I want to replace all the words Event with the word Fatal in all lines containing the word ERR - but I also want to keep the output of the other lines not matching ERR Test.txt: Event 13 INF egegegege Event 14 INF... (4 Replies)
Discussion started by: frustrated1
4 Replies
Login or Register to Ask a Question