159

I have written a query:

function print_ui_hosts
{
local sql = "select ........."
print_sql "$ sql"
}

local sql - a very long string. Query is not formatted. How can I split a string into multiple lines?

jeb
  • 78,592
  • 17
  • 171
  • 225
user2219963
  • 1,741
  • 2
  • 11
  • 12

5 Answers5

219

simply insert new line where necessary

sql="
SELECT c1, c2
from Table1, Table2
where ...
"

shell will be looking for the closing quotation mark

Nik O'Lai
  • 3,586
  • 1
  • 15
  • 17
  • 13
    not a good solution if the sql query contains double-quotes. You will have to escape them and it will get messy. – dogbane Mar 15 '13 at 13:23
  • 17
    @dogbane doublequotes appear rarely in most SQL dialects, so in practice this is clean. – Iain Samuel McLean Elder Sep 07 '14 at 18:51
  • 5
    Then wrap the string in single quotes. – tripleee Jan 13 '16 at 10:05
  • Not sure why you want or need the leading line break. For my application I did not so I just started with `sql="SELECT c2, c2` – bhfailor Jan 30 '18 at 01:31
  • 1
    Funny that it seems too easy to be true. FYI, to add DQ, just create a variable DQ='\"' and then reference it in the statement with ${DQ}. – Timothy C. Quinn May 11 '19 at 00:03
  • If answer *How can I specify a multi-line shell variable?*, then the quotation limitation is certainly a snag. Should you find yourself using different types of quotes in a string, use a here document, see `man --pager='less -p here-documents' bash` and this answer https://stackoverflow.com/a/15429426/1236128. – Jonathan Komar Nov 28 '21 at 07:03
  • Note: you've got to do echo -n "$sql" to get this out right – toddmo Jul 12 '22 at 00:57
  • To avoid prepending a newline (while maintaining alignment), put a backslash (\\) on the first line. Verified identical variable contents with `hexdump -C` and it works on POSIX sh. – Elliot Killick Jul 25 '23 at 00:27
182

Use read with a heredoc as shown below:

read -d '' sql << EOF
select c1, c2 from foo
where c1='something'
EOF

echo "$sql"
dogbane
  • 266,786
  • 75
  • 396
  • 414
  • 68
    Note that `read` will have an exit code of 1 in this situation; if that matters (you are running with `set -e`, for example), you'll want add a `|| true` at the end of the first line. – chepner Mar 15 '13 at 12:53
  • @chepner you've saved me a year of baldness. how does `set -e` effects it? just curious. – chakrit Sep 22 '15 at 13:05
  • 4
    `set -e` exits the shell if a command has an "unanticipated" non-zero exit status. By "unanticipated", I mean it runs in a context where you aren't specifically looking at its exit status. `false` by itself, for instance, would exit the shell. `false || true` would not, since you are anticipating the non-zero exit status by specifying another command to run if the first fails. – chepner Sep 22 '15 at 13:32
  • 1
    The problem with set -e and read (see last exercise) is described here in detail: http://mywiki.wooledge.org/BashFAQ/105 – Niklas Peter Feb 25 '16 at 20:33
  • 10
    what does `-d ' '` do here? – hg_git Sep 26 '16 at 13:20
  • 2
    `read -d` appears to be a bashism, is there a way to do this in standard shell? – wilx Oct 10 '16 at 07:30
  • 6
    @hg_git Telling `read` not to stop reading when encountering a newline. – Cyker Dec 04 '16 at 01:55
  • 3
    You should use 'EOF' on the first line if the multiline string isn't meant to be parsed for shell variables and the like. – Michael Mol May 03 '17 at 15:33
95

I would like to give one additional answer, while the other ones will suffice in most cases.

I wanted to write a string over multiple lines, but its contents needed to be single-line.

sql="                       \
SELECT c1, c2               \
from Table1, ${TABLE2}      \
where ...                   \
"

I am sorry if this if a bit off-topic (I did not need this for SQL). However, this post comes up among the first results when searching for multi-line shell variables and an additional answer seemed appropriate.

islijepcevic
  • 1,139
  • 7
  • 17
8

Thanks to dimo414's answer to a similar question, this shows how his great solution works, and shows that you can have quotes and variables in the text easily as well:

example output

$ ./test.sh

The text from the example function is:
  Welcome dev: Would you "like" to know how many 'files' there are in /tmp?

  There are "      38" files in /tmp, according to the "wc" command

test.sh

#!/bin/bash

function text1()
{
  COUNT=$(\ls /tmp | wc -l)
cat <<EOF

  $1 Would you "like" to know how many 'files' there are in /tmp?

  There are "$COUNT" files in /tmp, according to the "wc" command

EOF
}

function main()
{
  OUT=$(text1 "Welcome dev:")
  echo "The text from the example function is: $OUT"
}

main
Community
  • 1
  • 1
Brad Parks
  • 66,836
  • 64
  • 257
  • 336
6

read does not export the variable (which is a good thing most of the time). Here's an alternative which can be exported in one command, can preserve or discard linefeeds, and allows mixing of quoting-styles as needed. Works for bash and zsh.

oneLine=$(printf %s \
    a   \
    " b "   \
    $'\tc\t'    \
    'd '    \
)
multiLine=$(printf '%s\n' \
    a   \
    " b "   \
    $'\tc\t'    \
    'd '    \
)

I admit the need for quoting makes this ugly for SQL, but it answers the (more generally expressed) question in the title.

I use it like this

export LS_COLORS=$(printf %s    \
    ':*rc=36:*.ini=36:*.inf=36:*.cfg=36:*~=33:*.bak=33:*$=33'   \
    ...
    ':bd=40;33;1:cd=40;33;1:or=1;31:mi=31:ex=00')

in a file sourced from both my .bashrc and .zshrc.

EndlosSchleife
  • 515
  • 7
  • 19