regex or other solution to alter string fragment

olegk25 · 06-04-2009, 05:22 AM

please help!

I'm new to Linux and I'm looking for a regex (sed) or other simple way to format the SQL like

Code:

select ABREV from LIMITS, ABREV where ABREV='US'

into

Code:

select ABREV from SCHEMA.LIMITS, SCHMEA.ABREV where ABREV='US'

I've tried

Code:

sed 's/\(select.*from[ ]*\)\([A-Za-z0-9_]\)\(.*\)/\1SCHEMA.\2\3/'

but it produces only

Code:

select ABREV from SCHEMA.LIMITS, ABREV where ABREV='US'

any help please?

P.S. There may be any number of table names

colucix · 06-04-2009, 05:38 AM

Try this awk code:

Code:

/select/{
 for ( i=1; i<=NF; i++ ) {
   if ( $i == "where" )
      break
   if ( to_sub )
      sub(/.*/,"SCHEMA."$i,$i)
   if ( $i == "from" )
      to_sub = 1
 }
}1

it checks for "from" and "where" words in the select statement and alter the embedded words accordingly. Awk cannot modify the input file on the fly, so you have to redirect the output to a new file:

Code:

awk -f test.awk infile > oufile

where test.awk is the awk script above.