How to populate a SQL in query

Question

I have a generic SQL query that I'd like to use bash to populate all of the rows with from a grep command that I get the strings from. The amount of strings that I get from the file I am pulling the strings from varies as well.

Example query:

    select * from table_name
    where column_name in ('string',
    'string',
    'string',
    'string',
    'string',
    'string',
    'string',)

Basically I am looking at email message IDs and I get an output of message IDs. One string per line.

grep -Eoh "[a-z0-9]{0,9}\.0\.[0-9]{0,9}\.00\-[0-9]{0,9}\.[0-9]{0,9}\.[a-z0-9{0,10}\.hostname.com" *filename.txt 

62e60645.0.2279968.00-2316.3908868.d02c12m066.hostname.com
3ebe0645.0.522591.00-2200.908364.a02c11m025.hostname.com
356f0645.0.1401456.00-2085.2306795.g02c12m014.hostname.com
9a001645.0.1594149.00-1533.2646910.y01c12m093.hostname.com

and so on.

What I would like to do is have all of the strings from my grep command automatically inserted in to the query so I can simply paste the string in and run in.

Answer 1

$ echo 'select * from table_name where column_name in ('"$(grep -Eoh '[a-z0-9]{0,9}\.0\.[0-9]{0,9}\.00\-[0-9]{0,9}\.[0-9]{0,9}\.[a-z0-9]{0,10}\.hostname.com' *filename.txt | awk -v ORS=', ' '{print "\047"$0"\047"}')"')'
select * from table_name where column_name in ('62e60645.0.2279968.00-2316.3908868.d02c12m066.hostname.com', '3ebe0645.0.522591.00-2200.908364.a02c11m025.hostname.com', '356f0645.0.1401456.00-2085.2306795.g02c12m014.hostname.com', '9a001645.0.1594149.00-1533.2646910.y01c12m093.hostname.com', )

Fixed a typo in your grep regex. You missed a closing ] on the last range.

The internal awk command does the line-to-list formatting.

awk -v ORS=', ' '{print "\047"$0"\047"}')"'

-v ORS=', ' set ORS (the output record separator to ,)

{print "\047"$0"\047"} print each line with ' before and after it with the value of ORS added at the end.

Answer 2

You can also accomplish it by iterating line by line and echoing:

#!/bin/bash
echo 'select * from table_name where column_name in ('
{
    read
    echo -n "'$REPLY'"
    while read; do
        echo -ne ",\n'$REPLY'"
    done
} < <(grep -Eoh '[a-z0-9]{0,9}\.0\.[0-9]{0,9}\.00\-[0-9]{0,9}\.[0-9]{0,9}\.[a-z0-9]{0,10}\.hostname.com' *filename.txt)
echo ')'

Here $REPLY contains the read lines (one by one) from the input provided by the process substitution (<(grep ...)).