Using sed to replace text in a template file with variable containing quotes

I'm trying to do the following. I'd like to have users provide me a file that has a list of IDs in it. It would look like this:

41aeb908-dfc7-4cf8-8285-31ca184dc1c5
da877ffa-49bc-4f07-b692-4873870fcb37
a555cdd0-e100-42cb-a355-140de7958b36

The file may have 10 or 100 of these lines in there. It will vary. I would then like to feed these to a SQL Query using SQLPlus. in order to do that, I am going to need to put quotes around each of these and add commas after each line.

echo "Formatting XIDs for query..."
formattedtxs=`sed -e "s/^/'/g" -e "s/$/'/g" -e "s/$/,/g" $1`

$1 will have the filename to be worked on. Then i have a file that I will be putting these into.

select distinct tt.transaction_id||','||tt.entity_id||','||cm.user_id
from transaction_tracker tt, course_membership cm
tt.entity_id = cm.course_id and
tt.transaction_id IN (XXXXXXXX);

the idea that开发者_开发技巧 I had was to put those XXXXs in there as my template, have sed find that and replace it with my text.

echo "Substituting XIDs into query template..."
sed "s/XXXXXXXX/$formattedtxs/" < query.template >query.tmp

It doesn't work because it gives the error unterminated `s' command. I'm guess it is because my formattedtxs variable itself has single quotes in it. Any ideas? The result of the first sed command is this

'41aeb908-dfc7-4cf8-8285-31ca184dc1c5', 'da877ffa-49bc-4f07-b692-4873870fcb37', 'a555cdd0-e100-42cb-a355-140de7958b36', '2b7794f5-9811-4cf3-bd42-1d459d8cb6eb', '6133179e-4e4c-4917-a132-1ee03ab88465', '81343735-e943-4084-ab9f-86f8b5aedfa9',

Bonus points if you can get rid of that last comma.

Assuming you're not married to sed and can use a simple bash script, you can do this:

cat $1 | while read line; do echo -n "'$line',"; done | sed 's/,$//'; echo

Where $1 is the file to be read.

Save this to file "mksql.bash" or so...

sed "s/REPLACEME/$(sed "s/.*/'&'/" | paste -s -d, -)/" < template_file.txt

and use it like:

bash mksql.bash < your_id_file

will produce

select distinct tt.transaction_id||','||tt.entity_id||','||cm.user_id
from transaction_tracker tt, course_membership cm
tt.entity_id = cm.course_id and
tt.transaction_id IN ('41aeb908-dfc7-4cf8-8285-31ca184dc1c5','da877ffa-49bc-4f07-b692-4873870fcb37','a555cdd0-e100-42cb-a355-140de7958b36');

the "template_file.txt" contain your sql template:

select distinct tt.transaction_id||','||tt.entity_id||','||cm.user_id
from transaction_tracker tt, course_membership cm
tt.entity_id = cm.course_id and
tt.transaction_id IN (REPLACEME);

I've replaced the much XX..X with the "REPLACEME" because i'm lazy counting Xes

btw, welcome in SO... :)

You can do it with one sed command with a little care:

sed -e '1i\
        select distinct tt.transaction_id||','||tt.entity_id||','||cm.user_id\
        from transaction_tracker tt, course_membership cm\
        tt.entity_id = cm.course_id and\
        tt.transaction_id IN (' \
    -e "s/.*/'&',/" \
    -e "\$a\
       'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')" > query.tmp

The entry with lots of x's is clearly not a valid GUID (UUID?) and therefore won't be matched, but will neutralize the trailing comma on the last valid GUID.

If you want the material to come from a template file, you can use '-e "1r $template_file"' instead of the insert command. You can add trailing material to the end with '-e "\$r $trailing_file"'. Etc.

A large part of the trickery here is dealing with the shell quoting interacting with SQL quoting. It is hard, but very important, to keep track of which process is getting to see what.

Pure Bash:

declare -a a=( $( cat $1 ) )    # IDs into an array
result="${a[*]}"                # array to string, blank as separator
result=\'${result// /\', \'}\'  # substitute blanks with ', ' / add first, last '

echo -e "${result}"

This gives:

'41aeb908-dfc7-4cf8-8285-31ca184dc1c5', 'da877ffa-49bc-4f07-b692-4873870fcb37', 'a555cdd0-e100-42cb-a355-140de7958b36'