Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Find 5 lines and replace with 18 line in sql file where it contains multiple blocks.

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Find 5 lines and replace with 18 line in sql file where it contains multiple blocks.
# 1  
Old 09-17-2009
Find 5 lines and replace with 18 line in sql file where it contains multiple blocks.
My sql file xyz_abc.sql in this file there are multiple sql block in this block I need to find the following block

Code:
rem Subset Rows (&&tempName.*)
CREATE   VIEW &&tempName.* AS
SELECT   *
FROM     &&tempName.*
WHERE    f is not null

and replace with following code

Code:
rem Subset Rows (&&tempName.*)
CREATE   VIEW &&tempName.* AS
SELECT  a a,b b,c c,d d,e e,f f,
 g g,h h,i i,j j,k k,l l,
 m m,n n,o o,p p,q q,r r,
 s s,t t,u u,v v,w w,x x,
 y y,z z,a a,b b,c c,d d,
 e e,f f,g g,h h,i i,j j,
 k k,l l,m m,n n,o o,p p,
 q q,r r,s s,t t,u u,v v,
 w w,x x,y y,z z,X1 X1, X2 X2,
 X3 X3, X4 X4, X5 X5, X6 X6, X7 X7, X8 X8, X9 X9, X10 X10, 
 X11 X11, X12 X12, X13 X13, X14 X14, X15 X15, X16 X16,
 X17 X17, X18 X18, X19 X19, X20 X20, X21 X21, X22 X22, 
 X23 X23, X24 X24, X25 X25, X26 X26, X27 X27, X28 X28, 
 X29 X29, X30 X30
FROM     &&tempName.*
WHERE    f is not null
I have written the follwoing program using perl but its not working.
 
perl -i -pe 's/
rem Subset Rows (&&tempName.*)
CREATE   VIEW &&tempName.* AS
SELECT   *
FROM     &&tempName.*
WHERE    f is not null/
rem Subset Rows (&&tempName.*)
CREATE   VIEW &&tempName.* AS
SELECT  a a,b b,c c,d d,e e,f f,
 g g,h h,i i,j j,k k,l l,
 m m,n n,o o,p p,q q,r r,
 s s,t t,u u,v v,w w,x x,
 y y,z z,a a,b b,c c,d d,
 e e,f f,g g,h h,i i,j j,
 k k,l l,m m,n n,o o,p p,
 q q,r r,s s,t t,u u,v v,
 w w,x x,y y,z z,X1 X1, X2 X2,
 X3 X3, X4 X4, X5 X5, X6 X6, X7 X7, X8 X8, X9 X9, X10 X10, 
 X11 X11, X12 X12, X13 X13, X14 X14, X15 X15, X16 X16,
 X17 X17, X18 X18, X19 X19, X20 X20, X21 X21, X22 X22, 
 X23 X23, X24 X24, X25 X25, X26 X26, X27 X27, X28 X28, 
 X29 X29, X30 X30
FROM     &&tempName.*
WHERE    EGTESTCD is not null/g' xyz_abc.sql

Please suggest.
Last edited by Franklin52; 09-17-2009 at 09:00 AM.. Reason: Please use code tags!
# 2  
Old 09-17-2009
Try...
Code:
 
sed 's/\(SELECT\) .*/\1 a a,b b,c c,d d,e e,f f,\
 g g,h h,i i,j j,k k,l l,\
 m m,n n,o o,p p,q q,r r,\
 s s,t t,u u,v v,w w,x x,\
 y y,z z,a a,b b,c c,d d,\
 e e,f f,g g,h h,i i,j j,\
 k k,l l,m m,n n,o o,p p,\
 q q,r r,s s,t t,u u,v v,\
 w w,x x,y y,z z,X1 X1, X2 X2,\
 X3 X3, X4 X4, X5 X5, X6 X6, X7 X7, X8 X8, X9 X9, X10 X10,\
 X11 X11, X12 X12, X13 X13, X14 X14, X15 X15, X16 X16,\
 X17 X17, X18 X18, X19 X19, X20 X20, X21 X21, X22 X22,\
 X23 X23, X24 X24, X25 X25, X26 X26, X27 X27, X28 X28,\
 X29 X29, X30 X30/g' infile

# 3  
Old 09-18-2009
Hi I tried with the above code but its not working.

I will explain you once again

In my xyz_abc.sql file there are multiple blocks.
Just imagine there are 25 blocks in xyz_abc.sql file in that block I need to find the following block.

Code:
rem Subset Rows (&&tempName.*)
CREATE   VIEW &&tempName.* AS
SELECT   *
FROM     &&tempName.*
WHERE    EGTESTCD is not null

Once this get done in xyz_abc.sql. I need to replace with the following block

Code:
rem Subset Rows (&&tempName.*)
CREATE   VIEW &&tempName.* AS
SELECT   STUDYID STUDYID, DOMAIN DOMAIN, USUBJID USUBJID, ggg(y) EGSEQ,
                  EGGRPID EGGRPID, EGREFID EGREFID, EGSPID EGSPID, EGTESTCD
                  EGTESTCD, EGTEST EGTEST, EGCAT EGCAT, EGSCAT EGSCAT, EGPOS
                  EGPOS, EGORRES EGORRES, EGORRESU EGORRESU, EGNRIND EGNRIND,
                  EGSTRESC EGSTRESC, EGSTRESN EGSTRESN, EGSTRESU EGSTRESU,
                  EGSTAT EGSTAT, EGREASND EGREASND, EGXFN EGXFN, EGNAM EGNAM,
                  EGLOINC EGLOINC, EGMETHOD EGMETHOD, EGBLFL EGBLFL, EGDRVFL
                  EGDRVFL, EGEVAL EGEVAL, VISIT VISIT, VISITNUM VISITNUM,
                  VISITDY VISITDY, EGDTC EGDTC, EGDY EGDY, EGTPTNUM EGTPTNUM,
                  EGTPT EGTPT, EGELTM EGELTM, EGTPTREF EGTPTREF, X1 X1, X2 X2,
                  X3 X3, X4 X4, X5 X5, X6 X6, X7 X7, X8 X8, X9 X9, X10 X10, X11
                  X11, X12 X12, X13 X13, X14 X14, X15 X15, X16 X16, X17 X17, X18
                  X18, X19 X19, X20 X20, X21 X21, X22 X22, X23 X23, X24 X24, X25
                  X25, X26 X26, X27 X27, X28 X28, X29 X29, X30 X30
FROM     &&tempName.*
WHERE    EGTESTCD is not null

Please suggest some code.Keep waiting for your reply.
Last edited by vgersh99; 09-18-2009 at 03:10 PM.. Reason: code tags, PLEASE!
# 4  
Old 09-18-2009
To keep the forums high quality for all users, please take the time to format your posts correctly.

First of all, use Code Tags when you post any code or data samples so others can easily read your code. You can easily do this by highlighting your code and then clicking on the # in the editing menu. (You can also type code tags [code] and [/code] by hand.)

Second, avoid adding color or different fonts and font size to your posts. Selective use of color to highlight a single word or phrase can be useful at times, but using color, in general, makes the forums harder to read, especially bright colors like red.

Third, be careful when you cut-and-paste, edit any odd characters and make sure all links are working property.

Thank You.

The UNIX and Linux Forums
# 5  
Old 09-18-2009
Save the replacement text in a file ( say repl_file ) and try,

Code:
perl -i.bak -0pe 's/rem.*?f is not null\n/qx(cat repl_file)/es' sql_file

Original file is saved in file.bak, just in case.

P.S.
The text is more readable if you place code tags around the samples/codes.
# 6  
Old 09-22-2009
The above code is excuting but not replacing the file which is present in repl_file.Please suggest

---------- Post updated at 11:14 PM ---------- Previous update was at 11:10 PM ----------

Thanks in advance.and Thanks for your code.

Please suggest
# 7  
Old 09-22-2009
Quote:
Originally Posted by Zaheer.mic
...
In my xyz_abc.sql file there are multiple blocks.
Just imagine there are 25 blocks in xyz_abc.sql file in that block I need to find the following block.

Code:
rem Subset Rows (&&tempName.*)
CREATE   VIEW &&tempName.* AS
SELECT   *
FROM     &&tempName.*
WHERE    EGTESTCD is not null

...
(1) Yes but how is this block different from the other 24 ? You will need this information in order to identify and replace this block correctly (out of the 25).

(2) Are all the blocks identical ? If so, which block do you want replaced ? The 1st, 2nd, 25th ? Or all ?

To give us a better idea, post a short excerpt of your target file that clearly demonstrates the difference(s) between the block of interest and the rest.

tyler_durden
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Script using awk to find and replace a line, how to ignore comment lines

Hello, I have some code that works more or less. This is called by a make file to adjust some hard-coded definitions in the src code. The script generated some values by looking at some of the src files and then writes those values to specific locations in other files. The awk code is used to... (3 Replies)
Discussion started by: LMHmedchem
3 Replies

2. Shell Programming and Scripting

Shell Script to find common lines and replace next line

I want to find common line in two files and replace the next line of first file with the next line of second file. (sed,awk,perl,bash any solution is welcomed ) Case Ignored. Multiple Occurrence of same line. File 1: hgacdavd sndm,ACNMSDC msgid "Rome" msgstr "" kgcksdcgfkdsb... (4 Replies)
Discussion started by: madira
4 Replies

3. Shell Programming and Scripting

sed multiple multi line blocks of text containing pattern

Hi, I have a log file which has sessionids in it, each block in the log starts with a date entry, a block may be a single line or multiple lines. I need to sed (or awk) out the lines/blocks with that start with a date and include the session id. The files are large at several Gb. My... (3 Replies)
Discussion started by: andyatit
3 Replies

4. Shell Programming and Scripting

Script to find & replace a multiple lines string across multiple php files and subdirectories

Hey guys. I know pratically 0 about Linux, so could anyone please give me instructions on how to accomplish this ? The distro is RedHat 4.1.2 and i need to find and replace a multiple lines string in several php files across subdirectories. So lets say im at root/dir1/dir2/ , when i execute... (12 Replies)
Discussion started by: spfc_dmt
12 Replies

5. Shell Programming and Scripting

Find and replace multiple lines

I have a section of text in file A, see below # falkdjf lkjadf lkjadf lkajdf lkajdf lkajdf lkjadf lkjadf 234.234.2.234 lkjlkjlk 234.234.3.234 # Only the first line with "# falkdjf lkjadf lkjadf" is unique in the file. The new section that I want to overwrite the old section above is in... (1 Reply)
Discussion started by: jyang72211
1 Replies

6. Shell Programming and Scripting

replace (sed?) a single line/string in file with multiple lines (string) from another file??

Can someone tell me how I can do this? e.g: Say file1.txt contains: today is monday the 22 of NOVEMBER 2010 and file2.txt contains: the 11th month of How do i replace the word NOVEMBER with (5 Replies)
Discussion started by: tuathan
5 Replies

7. UNIX for Dummies Questions & Answers

grep command to find multiple strings in multiple lines in a file.

I want to search files (basically .cc files) in /xx folder and subfolders. Those files (*.cc files) must contain #include "header.h" AND x() function. I am writing it another way to make it clear, I wanna list of *.cc files that have 'header.h' & 'x()'. They must have two strings, header.h... (2 Replies)
Discussion started by: ritikaSharma
2 Replies

8. Shell Programming and Scripting

Single/Multiple Line with Special characters - Find & Replace in Unix Script

Hi, I am creating a script to do a find and replace single/multiple lines in a file with any number of lines. I have written a logic in a script that reads a reference file say "findrep" and populates two variables $FIND and $REPLACE print $FIND gives Hi How r $u Rahul() Note:... (0 Replies)
Discussion started by: r_sarnayak
0 Replies

9. Shell Programming and Scripting

sed find and replace multiple lines

I am new to linux and would like to modify the contents of a file preferably using a one line. The situation is as follows <start> some lines "I am the string" "replace string" more lines here <end> In the above example,On encountering "I am the string", the "replace string "should be... (6 Replies)
Discussion started by: supersimha
6 Replies

10. Shell Programming and Scripting

replace multiple lines in file

Hello I am a beginner in shell script. I was trying to find a way to replace multiple lines of a file with different set of multiple line. sed -n '/begin/,/end/p' < sample1.txt >test.txt UNEDITED=`cat test.txt` vi test.txt EDITED=`cat test.txt`... (2 Replies)
Discussion started by: nox
2 Replies
Login or Register to Ask a Question