how to replace a variable in shell script string
In my terminal:
$ SQL="SELECT PX_PROMOTION_ID, PRIORITY, STATUS, EXCLSVE, TYPE, PERORDLMT, PERSHOPPERLMT, TOTALLMT, RSV_INT, PX_GROUP_ID, CAMPAIGN_ID, STOREENT_ID, VERSION, REVISION, EFFECTIVE, TRANSFER, CDREQUIRED, EXPIRE, LASTUPDATEBY, TO_CHAR(LASTUPDATE, 'YYYYMMDD HH24MMSS') AS LASTUPDATE, TO_CHAR(STARTDATE, 'YYYYMMDD HH24MMSS') AS STARTDATE, TO_CHAR(ENDDATE, 'YYYYMMDD HH24MMSS') AS ENDDATE, TO_CHAR(RSV_TIME, 'YYYYMMDD HH24MMSS') AS RSV_TIME, RSV_REAL, TGTSALES, NAME, CODE, RSV_VCH, OPTCOUNTER FROM PX_PROMOTION WHERE LASTUPDATE BETWEEN (SELECT MAX(BATCHSTART) FROM XRPTEBATCHCONTROL) AND TIMESTAMP('\$BATCH_END')"
$ # (observe: I escaped the $ sign to have the same variable as you)
$ echo "$SQL"
SELECT PX_PROMOTION_ID, PRIORITY, STATUS, EXCLSVE, TYPE, PERORDLMT, PERSHOPPERLMT, TOTALLMT, RSV_INT, PX_GROUP_ID, CAMPAIGN_ID, STOREENT_ID, VERSION, REVISION, EFFECTIVE, TRANSFER, CDREQUIRED, EXPIRE, LASTUPDATEBY, TO_CHAR(LASTUPDATE, 'YYYYMMDD HH24MMSS') AS LASTUPDATE, TO_CHAR(STARTDATE, 'YYYYMMDD HH24MMSS') AS STARTDATE, TO_CHAR(ENDDATE, 'YYYYMMDD HH24MMSS') AS ENDDATE, TO_CHAR(RSV_TIME, 'YYYYMMDD HH24MMSS') AS RSV_TIME, RSV_REAL, TGTSALES, NAME, CODE, RSV_VCH, OPTCOUNTER FROM PX_PROMOTION WHERE LASTUPDATE BETWEEN (SELECT MAX(BATCHSTART) FROM XRPTEBATCHCONTROL) AND TIMESTAMP('$BATCH_END')
$ BATCH_END="2012-11-14 17:06:13"
$ echo "$BATCH_END"
2012-11-14 17:06:13
$ # Now the replacement:
$ echo "${SQL//\$BATCH_END/$BATCH_END}"
SELECT PX_PROMOTION_ID, PRIORITY, STATUS, EXCLSVE, TYPE, PERORDLMT, PERSHOPPERLMT, TOTALLMT, RSV_INT, PX_GROUP_ID, CAMPAIGN_ID, STOREENT_ID, VERSION, REVISION, EFFECTIVE, TRANSFER, CDREQUIRED, EXPIRE, LASTUPDATEBY, TO_CHAR(LASTUPDATE, 'YYYYMMDD HH24MMSS') AS LASTUPDATE, TO_CHAR(STARTDATE, 'YYYYMMDD HH24MMSS') AS STARTDATE, TO_CHAR(ENDDATE, 'YYYYMMDD HH24MMSS') AS ENDDATE, TO_CHAR(RSV_TIME, 'YYYYMMDD HH24MMSS') AS RSV_TIME, RSV_REAL, TGTSALES, NAME, CODE, RSV_VCH, OPTCOUNTER FROM PX_PROMOTION WHERE LASTUPDATE BETWEEN (SELECT MAX(BATCHSTART) FROM XRPTEBATCHCONTROL) AND TIMESTAMP('2012-11-14 17:06:13')
Done!
You need to quote the first $
so that it does not get expanded as a shell variable.
echo "$SQL" | sed -e "s/'\$BATCH_END'/'$BATCH_END'/g"
… Or choose an easier placeholder, something like @BATCH_END@
for instance.
To assign the result back to $SQL
you will need some more shell escaping:
SQL=`echo "$SQL" | sed -e "s/'\\\$BATCH_END'/'$BATCH_END'/g"`
You are missing the end of that single-quote pair in your script.
Change from:
echo $SQL | sed -e "s/'$BATCH_END/$BATCH_END/g"
To:
echo $SQL | sed -e "s/\$BATCH_END/$BATCH_END/g"
Updated - as per followup comment:
To save the result of the above replacement back into $SQL
, do either of the following:
# Preferred way
SQL=$(echo $SQL | sed -e "s/\$BATCH_END/$BATCH_END/g")
# Old way
SQL=`echo $SQL | sed -e "s/\$BATCH_END/$BATCH_END/g"`
This is called command substitution. Either syntax ($(...)
vs. enclosure by backticks) works, but the preferred one allows you to do nesting.
The preferred-preferred way: Herestring
This is probably a bit more advanced than what you care about, but doing it in the following way will save you a subprocess from having to use echo
unnecessarily:
SQL=$(sed -e "s/\$BATCH_END/$BATCH_END/g" <<< $SQL)