Menu
I'm having problems getting this to work...
I have a variable that is holding a SQL to with a placeholder:
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') I have another variable that holds the value:
echo $BATCH_END 2012-11-14 17:06:13 I want to replace the placeholder with the value. I'm not particularly great at Unix scripting, but I've tried this:
echo $SQL | sed -e "s/'$BATCH_END/$BATCH_END/g" but it still doesn't get replaced...
Can anyone help? I want to replace the placeholder, and keep the final string assigned to $SQL
I also need to know how to get the value of the output back into the variable, for example, I tried:
SQL=`echo "$SQL" | echo "${SQL//\$BATCH_END/$BATCH_END}"` 
414
To replace a substring with new value in a string in Bash Script, we can use sed command. sed stands for stream editor and can be used for find and replace operation. We can specify to sed command whether to replace the first occurrence or all occurrences of the substring in the string.
Replace String in a File with the `sed` Command'-i' option is used to modify the content of the original file with the replacement string if the search string exists in the file. 's' indicates the substitute command. 'search_string' contains the string value that will be searched in the file for replacement.
This can be done. Type var=$var and then expand and edit it. To expand, use Esc + Ctrl e (the default shortcut, check output of bind -p | grep shell-expand-line to confirm). Which you can then edit in an editor with Ctrl x Ctrl e ( edit-and-execute-command in readline terms).
Find and replace text within a file using sed command Use Stream EDitor (sed) as follows: sed -i 's/old-text/new-text/g' input.txt. The s is the substitute command of sed for find and replace. It tells sed to find all occurrences of 'old-text' and replace with 'new-text' in a file named input.txt.
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) 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!
42
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
