3

I have a files:

$cat file1.txt
1234|W
1345|S
8427|D
2132|C
3243|V

and my sql file is :

$cat select.sql
SELECT *
  FROM CUSTOMERS
 WHERE ID IN (FLAG);

and I have a shell with the next instructions

$cat replace.ksh
!#/bin/ksh

S_ids=`awk -F"|" '{print "\47" $1 "\47" ","}' file1.txt |sed '$s/,$//'`

sed -i "s/FLAG/${S_ids}/g" select.sql

I required put the values of the varible "S_ids" in the file select.sql input of word FLAG the result must by:

$cat select.sql
SELECT *
  FROM CUSTOMERS
 WHERE ID IN ('1234',
'1345',
'8427',
'2132',
'3243');

I have tried sed -i "s/FLAG/${S_ids}/g" select.sql but didn't get expected result.

  • is $i an exported variable that's set to "file1.txt"? Otherwise, I don't see it being set in replace.ksh. – Jeff Schaller Apr 29 '16 at 15:23
  • $i is file1.txt – Miguel Angel Apr 29 '16 at 15:26
  • Reality check please: your database field CUSTOMERS.ID really is a string rather than an integer? – roaima Apr 29 '16 at 15:47
  • Hello!!, The field CUSTOMER.ID is varchar could contains letters. – Miguel Angel Apr 29 '16 at 15:54
2

The reason this doesn't work can be inferred from the error message (that you omitted to provide):

sed: -e expression #1, char 14: unterminated `s' command

The sed command does not accept a multi-line value. You have to collapse your multiple lines into a single line. You could do this with a script such as this:

#!/bin/ksh
S_ids="'$(cut -d'|' -f1 file1.txt | tr '\n' ' ' | sed -e 's/ *$//' -e "s/ /','/g")'"
sed -i "s/FLAG/${S_ids}/g" select.sql

To see how S_ids is generated you can take each piece of the pipeline

cut -d'|' -f1 file.txt        # Extract first column
tr '\n' ' '                   # Convert newlines to spaces
sed -e 's/ *$//'              # Strip the trailing space
sed -e "s/ /','/g"            # Replace each remaining space with the three characters ','
0
#! /bin/ksh

inputfile='file1.txt'
sqlfile='select.sql'

S_ids=$(awk -F"|" '{gsub(/^|$/,"\\'\''",$1) ; print $1","}' "$inputfile" | 
        xargs | 
        sed -e 's/,$//')

sed -i "s/FLAG/${S_ids}/g" "$sqlfile"

Output:

$ cat select.sql 
SELECT *
  FROM CUSTOMERS
 WHERE ID IN ('1234', '1345', '8427', '2132', '3243');

This uses awk's gsub() function to add \' at the start and \', at the end of the first field of each line. It adds \' rather than just ' so that the single-quote survives the pipe through xargs.

Note the use of '\'' to embed a single-quote inside the single-quoted awk script - read that as "end-single-quote, escaped-single-quote, start-single-quote-again".

xargs with no command to run defaults to xargs echo so that puts all of the output lines (single quoted first fields of file1.txt) onto one line, separated by spaces. This will work unless there are many tens of thousands of lines in file1.txt - enough to exceed the maximum shell line length (typically 128KB or more).

Finally, it pipes the output of xargs into sed to strip the final , from the line.

If you prefer to have newlines after each ID in select.sql, change the last line of the script to:

sed -i "s/FLAG/${S_ids}/g; s/, /\n/g" "$sqlfile"

Output will be:

SELECT *
  FROM CUSTOMERS
 WHERE ID IN ('1234',
'1345',
'8427',
'2132',
'3243');
0
$ FLAG=$(awk -F\| '{printf("%s, ", $1)}' file1.txt)
$ echo $FLAG 
1234, 1345, 8427, 2132, 3243,
$ sed "s/FLAG/${FLAG%%, }/" select.sql
SELECT *
  FROM CUSTOMERS
 WHERE ID IN (1234, 1345, 8427, 2132, 3243);

This depends on the flag list being small enough to fit on the command line. If it's not, you can use getline in awk to process file1.txt, gathering the replacement string in a BEGIN pattern, and then process select.sql.

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.