Need help to execute sed command to replace a string with a variable

Question

I have a variable in data/time format and I want to replace it with the first parameter of below string in a file with the user input variable

TO_DATE('07/13/2021 00:30:00' 'MM/DD/YYYY HH24:MI:SS')

I want to replace '07/13/2021 00:30:00' with my variable.

I have a script which would take user input for start time and store it in a variable 'st'

I have a sql file which a static query. I need help in replacing TO_DATE('07/13/2021 00:30:00' with the variable and then the script would execute.

Tried below sed command and it's not working

sed -e 's/(.TO_DATE()[^ ]( .*)/\1'$st'\2/' /apps/tmp/abc.sql

Getting error message: Unknown option to `s' I am new to sed stuff. I am new to sed stuff

Can someone please help?

Answer 1

With sed, you'll want the s/// command, but beware that the the replacement text contails slashes. You can use different delimiters for the s command: here I'll use commas:

sed -E "s,(TO_DATE\(')[^']+,\1${timestamp}," file.sql

Demo:

$ cat file.sql
select * from table1
where created > TO_DATE('07/13/2021 00:30:00', 'MM/DD/YYYY HH24:MI:SS');

$ timestamp=$(date '+%m/%d/%Y %T')

$ sed -E "s,(TO_DATE\(')[^']+,\1${timestamp}," file.sql
select * from table1
where created > TO_DATE('07/14/2021 10:46:51', 'MM/DD/YYYY HH24:MI:SS');

The keys here are to use double quotes for the sed body to allow the shell to expand the variable, and to choose different delimiters for s///

And as commented, thoroughly validate the contents of the variable to keep out Little Bobby Tables