Appending a text to the top of each line

Shell Programming and Scripting
1

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

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

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

Hello John K,

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

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

Thanks,
R. Singh

1 Like
3

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

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

Hello John K,

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

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

Thanks,
R. Singh

1 Like
5

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
awk 'NF {val=$0;gsub(/.*[fF][rR][oO][mM] | [wW][hH][eE][rR][eE].*|;$/,"",val);print "PROMPT",val} 1'  file
1 Like
6

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.

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

Thanks,
R. Singh

1 Like
7

You can try this sed too

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