Visit Our UNIX and Linux User Community


Search a certain char and insert new text if a match found


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Search a certain char and insert new text if a match found
# 1  
Old 10-11-2016
Search a certain char and insert new text if a match found

Have a file which has the create statement like below
Code:
create table emp
( empno integer,
empname char(50))
primary index(empno);

i need to find a string starting with create and ends with semi-colon ;. if so insert the below statement before create statement
Code:
rename table emp to emp_rename;

and after semi-colon ; need to insert the below statements
Code:
insert into emp sel * from emp_rename;
drop table emp_rename;

o/p will be like this:
Code:
rename table emp to emp_rename;
create table emp
( empno integer,
empname char(50))
primary index(empno);
insert into emp sel * from emp_rename;
drop table emp_rename;

first string is always create and last char is ; in my file
can some one help on this

have tried awk
Code:
awk '/^create/;$/ ddlfile.txt

but after getting the pattern, not sure how to write the new statement above and below create statements.

Last edited by rbatte1; 10-12-2016 at 07:14 AM.. Reason: Converted wink to ICODE tagged semi-colon
# 2  
Old 10-11-2016
Code:
awk '
/create table/ {tn=$NF; print "rename table " tn " to " tn "_rename;"}
1
END {
   if (tn) {
      print "insert into " tn " sel * from " tn "_rename;"
      print "drop table " tn "_rename;"
   }
}
' infile

# 3  
Old 10-11-2016
Here is a sample awk script with updated sample input sql file.


input file :
Code:
$ cat 1.sql
-- sample header
create table emp
( empno integer,
empname char(50))
primary index(empno);
insert into emp values (1,'Stones');--sample insertion
-- sample comment

output of my script :
Code:
$ ./sqlmode.sh 1.sql
-- sample header
rename table emp to emp_rename;
 create table emp ( empno integer, empname char(50)) primary index(empno);
insert into emp sel * from emp_rename;
drop table emp_rename;
insert into emp values (1,'Stones');--sample insertion
-- sample comment

the script :
Code:
$ cat sqlmode.sh
# process sql files
awk '/create table emp/,/primary index/{
query = query " " $0
next
}
query > "" {
print "rename table emp to emp_rename;"
print query
print "insert into emp sel * from emp_rename;"
print "drop table emp_rename;"
query = ""
}
1' $1
#


Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Modify text file if found multiple pattern match for every line.

Looking for help, i have input file like below and want to modify to expected output, if can without create additional file, hope can direct modify it. have 2 thing need do. 1st is adding a word (testplan generation off) after ! ! IPG: Tue Aug 07 14:31:17 2018 2nd is adding... (16 Replies)
Discussion started by: kttan
16 Replies

2. Shell Programming and Scripting

Insert text after match in XML file

Having a little trouble getting this to work just right. I have xml files that i want to split some data. I have 2 <name> tags within the file I would like to take only the first tag and split the data. tag example. From this. TAB<Name>smith, john</Name> to TAB<Name>smith,... (8 Replies)
Discussion started by: whegra
8 Replies

3. Shell Programming and Scripting

Sed; insert text two lines above match

Hi! Considering below text, how would I use sed to insert text right below the v0005-line, using the SEPARATOR-line as a pattern to search for, so two lines above the separator? I can do it right above the separator, but not 2 lines... # v0004 - Some text # v0005 - More text #... (5 Replies)
Discussion started by: indo1144
5 Replies

4. UNIX for Dummies Questions & Answers

Search String, Out matched text and input text for no match.

I need to search a string for some specific text which is no big deal using grep. My problem is when the search fails to find the text. I need to add text like "na" when my search does not match. I have tried this command but it does not work when I put the command in a loop in a bash script: ... (12 Replies)
Discussion started by: jojojmac5
12 Replies

5. Shell Programming and Scripting

Displaying text till pattern match found in a line

Hi All, From the below line if we want to display all the text till found pattern dot/. I was trying with the below code but couldn't able to print text before the pattern. it display texts which is found after pattern. awk '/assed/{print;getline;print}' file_name | sed 's/^*. *//' input... (4 Replies)
Discussion started by: Optimus81
4 Replies

6. Shell Programming and Scripting

bash: need to have egrep to return a text string if the search pattern has NOT been found

Hello all, after spending hours of searching the web I decided to create an account here. This is my first post and I hope one of the experts can help. I need to resolve a grep / sed / xargs / awk problem. My input file is just like this: ----------------------------------... (6 Replies)
Discussion started by: bash4ever
6 Replies

7. Shell Programming and Scripting

bash script search file and insert character when match found

Hi I need a bash script that can search through a text file and when it finds 'FSS1206' I need to put a Letter F 100 spaces after the second instance of FSS1206 The format is the same throughout the file I need to repeat this on every time it finds the second 'FSS1206' in the file I have... (0 Replies)
Discussion started by: firefox2k2
0 Replies

8. Shell Programming and Scripting

Insert Block of Text into a File After a Ranged Search

Hello, I've been racking my brain trying to find a good way to accomplish a task. I need to insert a block of text into a file in the format of FirewallRuleSet proxy-users { FirewallRule allow to 0.0.0.0/0 } I need to insert this block of text (which could have sed special... (2 Replies)
Discussion started by: 0xception
2 Replies

9. Shell Programming and Scripting

Insert text file only after the first match with SED

Hello, I'm new in Shell scripting but i should write a script, which inserts the license header out of a txt-File into the files in our Projekt. For the Java classes it runs without Problems but for XML files not. At xml-files i have to put the license Header after the xml-Header (?xml... (1 Reply)
Discussion started by: PhoenixONE
1 Replies

10. UNIX for Advanced & Expert Users

Using egrep to search for Text and special char

Anyone is well-versed to use egrep to search a file for a line containing both: 1) AAA 2) $ I am having problem escaping the dollar sign when using egrep in conjunction with satisfying AAA as well. E.g. Text file Line 1 AAA Line 2 $$$ Line 3 AAA BBB $ Line 4 $$$ BBB AA will return me... (2 Replies)
Discussion started by: izy100
2 Replies

Featured Tech Videos