How to fill in SQL in a query
I have a generic SQL query that I would like to use bash to fill in all the lines from the grep command from which I get the lines. The number of lines I get from the file I pull from the lines also changes.
Request example:
select * from table_name
where column_name in ('string',
'string',
'string',
'string',
'string',
'string',
'string',)
Basically I search for email message ids and I get an output of message ids. One line 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
etc.
I would like all lines from my grep command to be automatically inserted into the request, so I can just insert the line and run it.
source to share
$ 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 grep regex. You missed the close ]
in the last range.
An internal awk command formats the string into a list.
awk -v ORS=', ' '{print "\047"$0"\047"}')"'
-v ORS=', '
set ORS
(output record separator to ,
)
{print "\047"$0"\047"}
print each line '
before and after with the value ORS
appended at the end.
source to share
You can also execute it by repeating 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 lines of reading (one by one) from the input provided by process override ( <(grep ...)
).
source to share