次のファイルがありますinput.txt
。
$cat input.txt
This is sample
Input file
To execute sql statement
以下の出力を変数に割り当てる必要があります。
X=('This is sample', 'Input file', 'To execute sql statement')
これにより、上記の文字列Xを条件のSQLクエリへの入力として使用できますIN
。
select * from table where columnname in X
ここで私を助けてください。
答え1
そしてbash
:
mapfile -t a < input.txt # read input file into array a
printf -v x "'%s'," "${a[@]}" # add single quotes and commas, save result in variable x
printf -v query 'select * from table where columnname in (%s);' "${x:0:-1}" # strip the last comma from x
printf '%s\n' "$query" # print query
出力:
select * from table where columnname in ('This is sample','Input file','To execute sql statement');
答え2
ファイルを1行ずつ配列に分割します。
# Tell bash that "lines" is an array
declare -a lines
# Read each line of the file
while read line; do
lines+=("$line")
done <input.txt
結果を印刷します。
# Show the first line
echo "${lines[0]}"
# Show all lines
echo "${lines[@]}"
ほとんどのデータベースCLIツールでは、ファイルを入力として実行できます。ポストグレスの例:
psql -f input.txt
答え3
短い冗談:
IFS=$'\n'; (read -r L && echo -n "X=('${L//\'/\"}'"; while read -r L; do echo -n ",'${L//\'/\"}'"; done; echo ")";) < input.txt; IFS=' '
(内のすべての一重引用符記号は'
二重引用符記号に置き換えられます"
。)
出力:
X=('This is sample','Input file','To execute sql statement')
または変数に割り当てます。
$ IFS=$'\n'; X=$( (read -r L && echo -n "('${L//\'/\"}'"; while read -r L; do echo -n ",'${L//\'/\"}'"; done; echo ")";) < input.txt); IFS=' ';
$ echo $X
('This is sample','Input file','To execute sql statement')
直す。最初のライナーは次のように説明しました。
#
# redefining IFS to read line by line ignoring spaces and tabs
# you can read more about at
# https://unix.stackexchange.com/questions/184863/what-is-the-meaning-of-ifs-n-in-bash-scripting
#
IFS=$'\n';
#
# next actions united inside of round brackets
# to read data from input.txt file
#
# first line from the file read separately,
# because should be printed with opening brackets
#
# between read and echo we use && to make sure
# that data are printed only if reading succeeded
# (that means that the file is not empty and we have reading permissions)
#
# also we print not just "X=($L
# but variable modified in the way to replace ' with "
# more details about it you can find at
# https://devhints.io/bash in Substitution section
# also quotes symbols inside of quoted text are backslash'ed
#
(read -r L && echo -n "X=('${L//\'/\"}'";
#
# now we read lines one by one as long as reading returns data
# (which is until we reach end of file)
# and each time printing: ,'LINE-DATA'
# I mean coma and data in single quotes like requested
# where the data are the lines where single quotes replaced
# with double quotes, the same as we did for the first line
while read -r L; do
echo -n ",'${L//\'/\"}'";
done;
#
# finally we print closing brackets for the data from file
#
# also we close round brackets uniting actions to read data from file
# and specifying file name from where we read data
echo ")";) < input.txt;
#
# at the end we change IFS back to original.
# actually for 100% accuracy it should be IFS=$' \t\n'
IFS=' '
答え4
移植可能なシェル関数のみを使用してください。
oIFS=$IFS # Store the IFS for later;
IFS= # Empty the IFS, to ensure blanks at the
# beginning/end of lines are preserved
while read -r line; do # Read the file line by line in a loop,
set -- "$@" "'""$line""'" # adding each line to the array of positional
done < input # parameters, flanked by single quotes
IFS=, # Set "," as the first character of IFS, which
# is used to separate elements of the array
# of positional parameters when expanded as
# "$*" (within double quotes)
X=\("$*"\) # Add parentheses while assigning to "X"
IFS=$oIFS # Restore the IFS - to avoid polluting later
# commands' environment
ご利用時に参考にしてくださいテキスト処理のためのシェルループの問題しかし、ここではファイルの内容がシェル変数で終わりたいので、これは意味があるかもしれません。
出力:
$ printf "%s %s\n" 'select * from table where columnname in' "$X"
select * from table where columnname in ('This is sample','Input file','To execute sql statement')
または、次のものを使用できますsed
。このスクリプトは、各入力行に一重引用符を追加し、すべての行を予約済みスペースに追加し、最後の行を追加した後、予約済みスペースの内容をパターンスペースに入れ、すべての項目<newline>
をコンマで置き換えます。 、テキストの先頭/末尾に角かっこを追加し、内容全体を印刷します。
X=$(sed -n '
s/^/'"'"'/
s/$/'"'"'/
H
1h
${
g
s/\n/,/g
s/^/(/
s/$/)/
p
}
' input)
もちろん、これらのメソッドは入力行に表示される単一引用符を処理しません。これにより、最終的にSQL文が破損したり、少なくとも予想される結果が生成されたりしません。