Unix/Linux Go Back    


Shell Programming and Scripting BSD, Linux, and UNIX shell scripting — Post awk, bash, csh, ksh, perl, php, python, sed, sh, shell scripts, and other shell scripting languages questions here.

Appending a text to the top of each line

Shell Programming and Scripting


Reply    
 
Thread Tools Search this Thread Display Modes
    #1  
Old Unix and Linux 4 Weeks Ago   -   Original Discussion by John K
John K's Unix or Linux Image
John K John K is offline
Registered User
 
Join Date: Sep 2012
Last Activity: 23 March 2018, 11:10 PM EDT
Posts: 94
Thanks: 126
Thanked 1 Time in 1 Post
Appending a text to the top of each line

Platform : Oracle Linux 6.8
Shell : bash

I have a file which has lines like below. These are SELECT queries (SQL)

In each line, I want the word just after FROM keyword to be copied and printed on the top along with the word PROMPT.
The words after FROM clause below are table names. So, they come like <schemaName>.<TableName> .

Both raw and expected output shown below


-- Raw file



Code:
SELECT AREA_ID, CODE, DESCRIPTION FROM SCOTT.SFO_AREA_MST;

SELECT EMP_ID, CODE, NAME, AREA_MST_ID, PARENT_ID FROM SCOTT.SFO_AREA_DTL where order_date > sysdate - 5 ;

SELECT OUTLET_AREA_ID, OUTLET_MST_ID, AREA_ID FROM APP_USR.SFO_OUTLET_AREA_MAPPING;

SELECT AREA_ID, AREA_USER_ID, AREA_MST_ID FROM SCOTT.SFO_USER_AREA_MAPPING;

SELECT EMP_ID, USER_ID, OUTLET_ID, AREA_ID, DEALER_ID FROM HR.EMP_MAPPING;

-- Expected output



Code:
PROMPT SCOTT.SFO_AREA_MST
SELECT AREA_ID, CODE, DESCRIPTION FROM SCOTT.SFO_AREA_MST;

PROMPT SCOTT.SFO_AREA_DTL
SELECT EMP_ID, CODE, NAME, AREA_MST_ID, PARENT_ID FROM SCOTT.SFO_AREA_DTL ;

PROMPT APP_USR.SFO_OUTLET_AREA_MAPPING
SELECT OUTLET_AREA_ID, OUTLET_MST_ID FROM APP_USR.SFO_OUTLET_AREA_MAPPING;

PROMPT SCOTT.SFO_USER_AREA_MAPPING
SELECT AREA_ID, AREA_USER_ID, AREA_MST_ID FROM SCOTT.SFO_USER_AREA_MAPPING;

PROMPT HR.EMP_MAPPING
SELECT EMP_ID, USER_ID, OUTLET_ID, AREA_ID, DEALER_ID FROM HR.EMP_MAPPING;

Sponsored Links
    #2  
Old Unix and Linux 4 Weeks Ago   -   Original Discussion by John K
RavinderSingh13's Unix or Linux Image
RavinderSingh13 RavinderSingh13 is offline Forum Advisor  
Registered User
 
Join Date: May 2013
Last Activity: 11 April 2018, 7:35 AM EDT
Location: Chennai
Posts: 2,715
Thanks: 609
Thanked 1,298 Times in 1,165 Posts
Hello John K,

Could you please try following and let me know if this helps you.


Code:
awk 'NF{gsub(/where.*|;$/,"");print "PROMPT",$NF ORS $0";"}'  Input_file

Thanks,
R. Singh
The Following User Says Thank You to RavinderSingh13 For This Useful Post:
John K (4 Weeks Ago)
Sponsored Links
    #3  
Old Unix and Linux 4 Weeks Ago   -   Original Discussion by John K
John K's Unix or Linux Image
John K John K is offline
Registered User
 
Join Date: Sep 2012
Last Activity: 23 March 2018, 11:10 PM EDT
Posts: 94
Thanks: 126
Thanked 1 Time in 1 Post
Thank You very much Ravinder
It works really well for my above requirement.


What if I have few words after the table name like the WHERE clause below.
Your above solution will not work for that(understandably).
What needs to be done to get it working

--Raw output

SELECT EMP_ID, CODE, NAME, AREA_MST_ID, PARENT_ID FROM SCOTT.HRTB_MASTER WHERE PSS_CREATE_DATE between SYSDATE-30 and sysdate;


--expected output



Code:
PROMPT SCOTT.HRTB_MASTER
SELECT EMP_ID, CODE, NAME, AREA_MST_ID, PARENT_ID FROM SCOTT.HRTB_MASTER WHERE PSS_CREATE_DATE between SYSDATE-30 and sysdate;

    #4  
Old Unix and Linux 4 Weeks Ago   -   Original Discussion by John K
RavinderSingh13's Unix or Linux Image
RavinderSingh13 RavinderSingh13 is offline Forum Advisor  
Registered User
 
Join Date: May 2013
Last Activity: 11 April 2018, 7:35 AM EDT
Location: Chennai
Posts: 2,715
Thanks: 609
Thanked 1,298 Times in 1,165 Posts
Hello John K,

Could you please try following and let me know if this helps you.



Code:
awk '{val=$0;gsub(/.*FROM | WHERE.*/,"",val);print "PROMPT",val ORS $0}'  Input_file

Thanks,
R. Singh
The Following User Says Thank You to RavinderSingh13 For This Useful Post:
John K (4 Weeks Ago)
Sponsored Links
    #5  
Old Unix and Linux 4 Weeks Ago   -   Original Discussion by John K
RudiC's Unix or Linux Image
RudiC RudiC is online now Forum Staff  
Moderator
 
Join Date: Jul 2012
Last Activity: 19 April 2018, 5:49 AM EDT
Location: Aachen, Germany
Posts: 12,487
Thanks: 400
Thanked 3,867 Times in 3,556 Posts
You could extend Ravindersingh13's proposal to
- work on upper and lower case keywords, both of which are allowed in SQL
- eliminate the trailing semicolon if where clause is missing
- keep empty lines for "paragraph separation"
like


Code:
awk 'NF {val=$0;gsub(/.*[fF][rR][oO][mM] | [wW][hH][eE][rR][eE].*|;$/,"",val);print "PROMPT",val} 1'  file

The Following User Says Thank You to RudiC For This Useful Post:
John K (4 Weeks Ago)
Sponsored Links
    #6  
Old Unix and Linux 4 Weeks Ago   -   Original Discussion by John K
RavinderSingh13's Unix or Linux Image
RavinderSingh13 RavinderSingh13 is offline Forum Advisor  
Registered User
 
Join Date: May 2013
Last Activity: 11 April 2018, 7:35 AM EDT
Location: Chennai
Posts: 2,715
Thanks: 609
Thanked 1,298 Times in 1,165 Posts
Hello John_K,

Also adding to RudiC's point, you could use following command too in case your strings Where OR WHERE OR where could work by this command.


Code:
awk '{val=toupper($0);gsub(/.*FROM | WHERE.*/,"",val);print "PROMPT",val ORS $0}'   Input_file

Thanks,
R. Singh
The Following User Says Thank You to RavinderSingh13 For This Useful Post:
John K (4 Weeks Ago)
Sponsored Links
    #7  
Old Unix and Linux 4 Weeks Ago   -   Original Discussion by John K
ctac_'s Unix or Linux Image
ctac_ ctac_ is offline
Registered User
 
Join Date: Aug 2017
Last Activity: 21 March 2018, 1:33 PM EDT
Posts: 21
Thanks: 2
Thanked 6 Times in 6 Posts
You can try this sed too


Code:
sed '/^$/!{h;s/.*[Ff][Rr][Oo][Mm] \([^ ;]*\).*/PROMPT \1/;G;}' infile


Last edited by ctac_; 4 Weeks Ago at 01:22 PM.. Reason: Missing filename
The Following User Says Thank You to ctac_ For This Useful Post:
John K (4 Weeks Ago)
Sponsored Links
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Linux More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Grabbing top line of text in a file and setting it to a variable digitalviking Shell Programming and Scripting 3 04-16-2012 10:12 AM
Increasing a number and appending it to next line of a text file smarty86 Shell Programming and Scripting 5 12-23-2010 12:21 PM
sed: appending alternate line after previous line rish_max Shell Programming and Scripting 4 02-10-2009 04:38 AM
appending text on top of another file anjali UNIX for Dummies Questions & Answers 3 10-21-2008 12:08 PM
Appending Text To Each Line That Matches Grep sysera Shell Programming and Scripting 4 06-16-2005 01:19 PM



All times are GMT -4. The time now is 05:52 AM.