Home Man
Search
Today's Posts
Register

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

Tags
shell scripts

👤 Login to reply

 
Thread Tools Search this Thread
# 1  
Old 03-21-2018
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;

# 2  
Old 03-21-2018
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 (03-21-2018)
# 3  
Old 03-21-2018
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 03-21-2018
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 (03-21-2018)
# 5  
Old 03-21-2018
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 (03-21-2018)
# 6  
Old 03-21-2018
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 (03-21-2018)
# 7  
Old 03-21-2018
You can try this sed too
Code:
sed '/^$/!{h;s/.*[Ff][Rr][Oo][Mm] \([^ ;]*\).*/PROMPT \1/;G;}' infile


Last edited by ctac_; 03-21-2018 at 01:22 PM.. Reason: Missing filename
The Following User Says Thank You to ctac_ For This Useful Post:
John K (03-21-2018)
👤 Login to reply

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
appending text to a file siteregsam Shell Programming and Scripting 3 02-01-2012 02:26 AM
sed - appending text citizencro UNIX for Dummies Questions & Answers 3 05-06-2011 08:01 PM
Appending two text files arunmanas UNIX for Dummies Questions & Answers 3 04-25-2011 01:37 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
Appending text to a file milink Shell Programming and Scripting 4 12-09-2010 07:42 AM
sed: appending alternate line after previous line rish_max Shell Programming and Scripting 4 02-10-2009 04:38 AM
Appending line ending with '}" to new line aismann Shell Programming and Scripting 4 08-13-2007 02:09 AM
Appending text of one file into other danland Shell Programming and Scripting 1 06-26-2007 05:16 AM
Appending the line number and a seperator to each line of a file ? pjcwhite Shell Programming and Scripting 4 03-21-2007 01:29 AM
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 03:17 AM.

Unix & Linux Forums Content Copyright©1993-2018. All Rights Reserved.
UNIX.COM Login
Username:
Password:  
Show Password