Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

awk help to do conditional find and replace

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk help to do conditional find and replace
# 1  
Old 03-12-2009
awk help to do conditional find and replace
Hi,

I have a Line input for awk as follows

DROP MATERIALIZED VIEW MCR.COMM_STACK;
CREATE MATERIALIZED VIEW "MCR"."COMM_STACK"
ON PREBUILT TABLE WITHOUT REDUCED PRECISION
USING INDEX
REFRESH FAST ON DEMAND START WITH sysdate+0 NEXT SYSDATE + 7
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS SELECT "COMM_STACK"."COMM_TS" "COMM_TS","COMM_STACK"."UPD_TS" "UPD_TS","COMM_STACK"."WORK_DIM_KEY" "WORK_DIM_KEY","COMM_STACK"."STOCK_DIM_KEY" "STOCK_DIM_KEY","COMM_STACK"."COMM_DIM_KEY" "COMM_DIM_KEY","COMM_STACK"."TRAN_REF_DIM_KEY" "TRAN_REF_DIM_KEY","COMM_STACK"."TRN_ID" "TRN_ID","COMM_STACK"."RESALE_DT" "RESALE_DT","COMM_STACK"."TRAN_CD" "TRAN_CD","COMM_STACK"."POST_DT" "POST_DT","COMM_STACK"."SETTLE_DT" "SETTLE_DT","COMM_STACK"."BRKR_ID" "BRKR_ID","COMM_STACK"."EXEC_BRKR_ID" "EXEC_BRKR_ID","COMM_STACK"."EXEC_TRDR_ID" "EXEC_TRDR_ID","COMM_STACK"."ORD_ID" "ORD_ID","COMM_STACK"."QUANTITY" "QUANTITY","COMM_STACK"."PRICE_LOCAL" "PRICE_LOCAL","COMM_STACK"."PRICE_USD" "PRICE_USD","COMM_STACK"."COMM_AMT_LOCL" "COMM_AMT_LOCL","COMM_STACK"."AMT_USD" "AMT_USD","COMM_STACK"."COMM_AMT_USD" "COMM_AMT_USD","COMM_STACK"."COMM_IMPUTE_USD" "COMM_IMPUTE_USD","COMM_STACK"."NOMINAL_AMT_USD" "NOMINAL_AMT_USD","COMM_STACK"."FACE_VALUE_USD" "FACE_VALUE_USD","COMM_STACK"."FWD_AMT_USD" "FWD_AMT_USD","COMM_STACK"."AMT_LOCL" "AMT_LOCL","COMM_STACK"."EXCH_RT" "EXCH_RT","COMM_STACK"."ACCT_DIM_KEY" "ACCT_DIM_KEY","COMM_STACK"."ORIG_FACE" "ORIG_FACE","COMM_STACK"."INT_AMT_LOCL" "INT_AMT_LOCL","COMM_STACK"."INT_AMT_BASE" "INT_AMT_BASE","COMM_STACK"."ORIG_COST_LOCL" "ORIG_COST_LOCL","COMM_STACK"."ORIG_COST_BASE" "ORIG_COST_BASE","COMM_STACK"."LT_GL_LOCL" "LT_GL_LOCL","COMM_STACK"."LT_GL_BASE" "LT_GL_BASE","COMM_STACK"."ST_GL_LOCL" "ST_GL_LOCL","COMM_STACK"."ST_GL_BASE" "ST_GL_BASE","COMM_STACK"."MISC_FEE" "MISC_FEE","COMM_STACK"."SEC_FEE_LOCL" "SEC_FEE_LOCL","COMM_STACK"."TAXES_LOCL" "TAXES_LOCL","COMM_STACK"."NEW_ELIM_CD" "NEW_ELIM_CD","COMM_STACK"."FIRST_LOT_DT" "FIRST_LOT_DT","COMM_STACK"."TRN_YLD" "TRN_YLD","COMM_STACK"."PERF_MONTH" "PERF_MONTH","COMM_STACK"."BOOK_YIELD" "BOOK_YIELD","COMM_STACK"."BOOK_YIELD_STAT" "BOOK_YIELD_STAT","COMM_STACK"."LT_GL_BASE_STAT" "LT_GL_BASE_STAT","COMM_STACK"."CORP_ACT_RT" "CORP_ACT_RT","COMM_STACK"."SETTLE_LOC" "SETTLE_LOC","COMM_STACK"."CLIENT_QLFD_QUAL" "CLIENT_QLFD_QUAL","COMM_STACK"."BOOK_VALUE_GAAP" "BOOK_VALUE_GAAP","COMM_STACK"."BOOK_VALUE_STAT" "BOOK_VALUE_STAT","COMM_STACK"."BOOK_VALUE_MGR" "BOOK_VALUE_MGR","COMM_STACK"."LT_GL_LOCL_STAT" "LT_GL_LOCL_STAT","COMM_STACK"."COST_BASE" "COST_BASE","COMM_STACK"."COST_LOCL" "COST_LOCL" FROM "MCR"."COMM_STACK"@CL.DIMM.COM "COMM_STACK"
/


Note: When this statement is viewed on a text editor all the statement from the "AS SELECT" to the end of line is displayed in one line.

I would like to have an awk script that looks for all the occurances of the pattern "," (With Quotes) and replace them with the new line character.

I was able to achive this with the following command in awk

(Save the above statement in a mview.sql file and run the below command)

nawk '{gsub("\",","\",\n\t\t", $0); print }' mview.sql | nawk '{gsub(" FROM ","\nFROM ", $0); print }' > mview.out

Output As Shown below (Only Part of the output shown)


DROP MATERIALIZED VIEW MCR.COMM_STACK;
CREATE MATERIALIZED VIEW "MCR"."COMM_STACK"
ON PREBUILT TABLE WITHOUT REDUCED PRECISION
USING INDEX
REFRESH FAST ON DEMAND START WITH sysdate+0 NEXT SYSDATE + 7
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS SELECT "COMM_STACK"."COMM_TS" "COMM_TS",
"COMM_STACK"."UPD_TS" "UPD_TS",
"COMM_STACK"."WORK_DIM_KEY" "WORK_DIM_KEY",
"COMM_STACK"."STOCK_DIM_KEY" "STOCK_DIM_KEY",
"COMM_STACK"."COMM_DIM_KEY" "COMM_DIM_KEY",
"COMM_STACK"."TRAN_REF_DIM_KEY" "TRAN_REF_DIM_KEY",
"COMM_STACK"."TRN_ID" "TRN_ID",
"COMM_STACK"."RESALE_DT" "RESALE_DT",
"COMM_STACK"."TRAN_CD" "TRAN_CD",
"COMM_STACK"."POST_DT" "POST_DT",
"COMM_STACK"."SETTLE_DT" "SETTLE_DT",
"COMM_STACK"."BRKR_ID" "BRKR_ID",
"COMM_STACK"."EXEC_BRKR_ID" "EXEC_BRKR_ID",
"COMM_STACK"."EXEC_TRDR_ID" "EXEC_TRDR_ID",
"COMM_STACK"."ORD_ID" "ORD_ID",
"COMM_STACK"."QUANTITY" "QUANTITY",
"COMM_STACK"."PRICE_LOCAL" "PRICE_LOCAL",
"COMM_STACK"."PRICE_USD" "PRICE_USD",
"COMM_STACK"."COMM_AMT_LOCL" "COMM_AMT_LOCL",
"COMM_STACK"."AMT_USD" "AMT_USD",
"COMM_STACK"."COMM_AMT_USD" "COMM_AMT_USD",
"COMM_STACK"."COMM_IMPUTE_USD" "COMM_IMPUTE_USD",
"COMM_STACK"."NOMINAL_AMT_USD" "NOMINAL_AMT_USD",
"COMM_STACK"."FACE_VALUE_USD" "FACE_VALUE_USD",
"COMM_STACK"."FWD_AMT_USD" "FWD_AMT_USD",
"COMM_STACK"."AMT_LOCL" "AMT_LOCL",
"COMM_STACK"."EXCH_RT" "EXCH_RT",
"COMM_STACK"."ACCT_DIM_KEY" "ACCT_DIM_KEY",
"COMM_STACK"."ORIG_FACE" "ORIG_FACE",
"COMM_STACK"."INT_AMT_LOCL" "INT_AMT_LOCL",
"COMM_STACK"."INT_AMT_BASE" "INT_AMT_BASE",
.
.
.
.
.
FROM "MCR"."COMM_STACK"@CL.DIMM.COM "COMM_STACK"

So Far i am happy with what i have seen.My question now is in the pattern i also have a few of such statements which are already in the next line as shown below

DROP MATERIALIZED VIEW MCR.COMM_STACK;
CREATE MATERIALIZED VIEW "MCR"."COMM_STACK"
ON PREBUILT TABLE WITHOUT REDUCED PRECISION
USING INDEX
REFRESH FAST ON DEMAND START WITH sysdate+0 NEXT SYSDATE + 7
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS SELECT "COMM_STACK"."COMM_TS" "COMM_TS",
"COMM_STACK"."UPD_TS" "UPD_TS",
"COMM_STACK"."WORK_DIM_KEY" "WORK_DIM_KEY",
"COMM_STACK"."STOCK_DIM_KEY" "STOCK_DIM_KEY",
"COMM_STACK"."COMM_DIM_KEY" "COMM_DIM_KEY",
"COMM_STACK"."TRAN_REF_DIM_KEY" "TRAN_REF_DIM_KEY",....

Now when a run the same nawk on such a file here is what happens

DROP MATERIALIZED VIEW MCR.COMM_STACK;
CREATE MATERIALIZED VIEW "MCR"."COMM_STACK"
ON PREBUILT TABLE WITHOUT REDUCED PRECISION
USING INDEX
REFRESH FAST ON DEMAND START WITH sysdate+0 NEXT SYSDATE + 7
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS SELECT "COMM_STACK"."COMM_TS" "COMM_TS",

"COMM_STACK"."UPD_TS" "UPD_TS",

"COMM_STACK"."WORK_DIM_KEY" "WORK_DIM_KEY",

"COMM_STACK"."STOCK_DIM_KEY" "STOCK_DIM_KEY",

"COMM_STACK"."COMM_DIM_KEY" "COMM_DIM_KEY",

"COMM_STACK"."TRAN_REF_DIM_KEY" "TRAN_REF_DIM_KEY",

"COMM_STACK"."TRN_ID" "TRN_ID",

"COMM_STACK"."RESALE_DT" "RESALE_DT",

.
.
.
.
.

So how can i avoid that from happening. Basically the question is if there is none after ", then do not substitute a new line just ignore. Go after only those that have a continuious line of such statements without a wrap.

Thanks and Kind Regards,
Rajan
Last edited by rajan_san; 03-12-2009 at 12:37 PM..
# 2  
Old 03-12-2009
Any Help....
# 3  
Old 03-13-2009
Try changing the pattern a little

Quote:
nawk '{gsub("\",\"","\",\n\t\t\"", $0); print }' mview.sql | nawk '{gsub(" FROM ","\nFROM ", $0); print }'
Instead of searching for ",
search for ","
# 4  
Old 03-13-2009
I think it works.. Thanks a lot for the tip.. Was really very useful...
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Find and replace in awk

I have a file that I am trying to find a specific word, then replace text within that string. file TestA2015 TestB2016 Example. Replace TestB2016 to TestB0000, so if TestB is found replace the original "2016" to "0000". Thank you :). awk tried awk '{ sub(/TestB$/, "0000", $6) }1'... (5 Replies)
Discussion started by: cmccabe
5 Replies

2. Shell Programming and Scripting

awk - find first occurence and replace it

Hello, I have a requirement to replace the whole string with first occurence of value of a key-value pair. I have to do this inside awk, as the data I need to work is inside an awk loop. value of my variable(say LogText) looks like: LogText= date=04Mar message=hello1 name=caq... (4 Replies)
Discussion started by: cool.aquarian
4 Replies

3. Shell Programming and Scripting

Find fields and replace using awk

Code: Using ksh Var1=`awk -F";" {print $1}' Input2.txt` cat Input1.txt | awk -F";" '{$3="Var1"}' > Output.txt (13 Replies)
Discussion started by: Roozo
13 Replies

4. Shell Programming and Scripting

Find and Replace in awk

Friends, I have more the thousand lines like this. check.cloud1.port=342 check.cloud2.port=5456 check.cloud3.port-4564 But we need to transfer it to _CHECK.CLOUD1.PORT_=342 _CHECK.CLOUD2.PORT_=5456 _CHECK.CLOUD3.PORT_=4564 Any one could pls help of this. Thanks in Advance ... (1 Reply)
Discussion started by: jothi basu
1 Replies

5. Shell Programming and Scripting

sed and awk -Find and Replace

All, I have thousands of lines in a file with following format DATA=_ONE_XXX_YYY_CCC_HHHG_ DATA1=_GGG_JJJJ_HHH_UUU_JJJJ_HHHH_LLL_ DATA3=_MMM_GG_NN_QQQQ_FFF_III_ I want to replace _ with . by ignoring the first (=_) and last (_) So that out put should looks like... (4 Replies)
Discussion started by: baluchen
4 Replies

6. Shell Programming and Scripting

awk conditional find

Hi, I have a file in the following format: aabbba 25.31806899 baaabb 38.21808852 cccccu 1.31819523 552258121.31818253 ffddybb 5.41815555 almcamc87561812689 223aqas5.661828345 adacaaaaaaa1821285 adacaaaaaaa1821286 smckaa 3.81828756 ada2512510c1821287 ada2522511c1821328... (4 Replies)
Discussion started by: alex2005
4 Replies

7. Shell Programming and Scripting

Conditional Search/Replace

I'm looking for an awk or (preferably) sed solution to search a pipe delimited file for any occurrence of an email address that does not include a designed domain, and replace the email address with a blank. E.g. hello|smith@designateddomain.com|jones@anotherdomain.edu|1234| turns into: ... (2 Replies)
Discussion started by: tiggyboo
2 Replies

8. Shell Programming and Scripting

find and replace issue using awk

Hi All, point 1. I have n number of environment variable files in different folders. All file names are ending with one thing common ie, *envset.sh point 2. All these contains Varilables and some other information like following *envset.sh ===>> ... (3 Replies)
Discussion started by: nitin.pathak
3 Replies

9. Shell Programming and Scripting

Simple find and replace with AWK

I am trying to write a find and replace script with AWK and I can't seem to get it to work. I need it to find this exact string *P*: and replace the P with a T or just replcare the whole thing with *T*:. this is what I have tried awk 'BEGIN {gsub(/\*P*:/,"\*T*:"); print}' ${INFILE} >... (4 Replies)
Discussion started by: wbshrk
4 Replies

10. Shell Programming and Scripting

awk find/replace

Greetings all. I have web site that has long option and switch lists. When I insert something new into these files, the lists need to be reordered. IE: 1 => apple 2 => pear 3 => bannana 4 => orange --------------------- Add grape as #2 1 => apple 2 => grape 3 => pear 4 =>... (2 Replies)
Discussion started by: RobertSubnet
2 Replies
Login or Register to Ask a Question