0

I have one sql file on which I'm doing cat and assigning to a variable say "SQL". I have different DATABASES and different TABLES in sql file. I'm trying to replace those tables and databases value from the file which have there values stored in it using sed, but I'm getting weird result every time I'm running it. Below is the approach I've tried:

sql file query:

SELECT *from DATABASE1.TABLE1
UNION ALL
SELECT *from DATABASE2.TABLE2
UNION ALL
SELECT *from DATABASE3.TABLE3
UNION ALL
SELECT *from DATABASE4.TABLE4

.sh file containing database and table details:

#!/bin/bash
DATABASE1="hr"
TABLE1="emp"
DATABASE2="account"
TABLE2="employee_details"
DATABASE3="payable"
TABLE3="job_details"
DATABASE4="dummy"
TABLE4="basic_details"

before using above value I'm sourcing .sh file so that I can get the value details on shell.


SQL=`cat query.sql`

query=$(echo $SQL| sed -e "s/DATABASE1/${DATABASE1}/;s/DATABASE2/${DATABASE2}/;
s/DATABASE3/${DATABASE3}/;s/DATABASE4/${DATABASE4}/;s/TABLE1/${TABLE1}/;
s/TABLE2/${TABLE2}/;s/TABLE3/${TABLE3}/;s/TABLE4/${TABLE4}/")

By using above approach I'm getting weird result but when I'm assigning database and table variables on shell and using in above query then I'm able to achieve the desired result. Please let me know what wrong I'm doing in case of .sh file approach.

  • 1
    $query should probably be $SQL? And I think there is a closing brace missing. – markgraf Aug 28 at 6:19
  • @markgraf yep,Updated the code. – sam Aug 28 at 6:24
  • 2
    I have no idea, why you cat the file, put it into a variable, echo that variable and pipe it into sed while you could directly give the file name as an argument to sed instead. Same weird result if you do it that way? – Philippos Aug 28 at 6:24
  • @Philippos, yep I have tried with giving file in sed command. same weird result I'm getting with that as well. – sam Aug 28 at 6:39
  • 1
1

There is already a tool for this: envsubst. It'll replace strings like $foo with the value of that variable, handling special characters nicely.

In any case you have a SQL syntax error: there should be a space after *.

  • I already tried the envsubst, but that too is not working. – sam Aug 28 at 8:49
  • It works for thousands of other people every day. There are plenty of examples on the web. – l0b0 Aug 28 at 10:00
  • Thank you for your suggestion. I made the changes in sed command and now it's working. – sam Aug 28 at 12:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.