BASH - How to read file contents to insert results into mysql WHERE clause

Question

I'm trying to make a file (call it accounts) and have a bash script (call it *name_checker*) loop through each line and make it into multiple strings to store in a variable called '$NAMES' for a mysql WHERE IN clause

For example:

My query in my script *name_checker* is basic:

SELECT * FROM table WHERE account_name IN ('$NAMES')

The values for the IN statement will need to be separated by commas and put into single quotes.

My accounts file will be names separated by newlines:

NAME1

NAME2

NAME3

So I would need my script (*name_checker*) to get rid of the newlines and surround each name with single quotes and separate them with a comma. The desired results when the script is run would be

SELECT * FROM table WHERE account_name IN ('NAME1','NAME2','NAME3')

I am having some difficulty with this and I'm not too familiar with using sed. Help is appreciated!

Answer 1

It can be done without calling external utilities.

cat >Accounts.txt <<XXX
NAME1
NAME2
NAME3
XXX

Script:

while read x; do NAMES="$NAMES,'$x'"; done <Accounts.txt
NAMES=${NAMES:1}
SQL='SELECT * FROM table WHERE account_name IN ('$NAMES')'
echo "$SQL"

Output:

SELECT * FROM table WHERE account_name IN ('NAME1','NAME2','NAME3')

Or it can be even simplified removing explicit loop

printf -v NAMES ",'%s'" $(<Accounts.txt)
NAMES=${NAMES:1}
echo "$NAMES";

Output:

'NAME1','NAME2','NAME3'