How to call a string by string from a file to use in for loop?

Shell Programming and Scripting
1

Hi,

I have a below sql query.

select * from table where name in ('${name});

I have a requirement to automate this as here i need to pass the string value name from txt file.
The text file contains the differnet special characters and i have some 200 names in the text file as follows.

3" - 11" DCBA-1234
5"-8" ABC MNOP-3765
8"-16" LMNOP ABCD-0302
ABC XYZ JKLM-1212
XYZ-1673

And my below code is not fetching the complete string value, How to achieve this?

for name in `cat file.txt`
do
select * from table where name in ('${name});
done

Any insights would be more helpful!!
Thanks in advance!!

2

Your shell will not be able to do a SQL SELECT. So you want either just echo/print it out or you will have to put your SQL-client here like sqlplus for example with oracle etc.
Also the single quote seems to be very lonely, means it is not closed by a second single quite.

You can try this:

$ while read LINE; do echo "select * from table where name in ('$LINE');"; done < infile
select * from table where name in ('3" - 11" DCBA-1234');
select * from table where name in ('5"-8" ABC MNOP-3765');
select * from table where name in ('8"-16" LMNOP ABCD-0302');
select * from table where name in ('ABC XYZ JKLM-1212');
select * from table where name in ('XYZ-1673');

It might be more efficient to add the other strings with a separating comma into the same statement so you fire only one statement instead of 200.

3

Thanks for the reply !

I am using Netezza and i have just given an example with a single query and i have given all the connection strings to access and once the first query returns the results then a set of queries needs to execute..so i have to put this in loop.

4

Else you could do something like this:

awk -v q=\' '
{
     s ? s=s ORS q $0 q : s=q $0 q
}
END{
     printf("select * from table where name in (%s);\n", s)
}
' ORS=, infile

Output:

select * from table where name in ('3" - 11" DCBA-1234','5"-8" ABC MNOP-3765','8"-16" LMNOP ABCD-0302','ABC XYZ JKLM-1212','XYZ-1673');