Adding breaks between every two words in bash

Question

My Query is:

QUERY="SELECT 'Your Name:', FullName, 'Your Phone:', Phone, 'Email:', Email FROM Table1 JOIN  WHERE EmpID=001;"

The output I get is

Your Name: Samanta Your Phone: 111-111-1111 Email: testemail@test.com

But I want this output when i print:

Your Name: Samanta
Your Phone: 111-111-1111
Email: testemail@test.com

I tried this:
output=executeSQLQuery "$QUERY" | tr -c ' ' '\n'

It is not working.
I am new to bash and sql. Please help me.

Answer 1

output=executeSQLQuery "$QUERY" |sed 's/: [^ ]* /& \n/g'

The above command yields

user@user:~> z="Your Name: Samanta Your Phone: 111-111-1111 Email: testemail@test.com"
user@user:~> echo $z | sed 's/: [^ ]* /& \n/g'
Your Name: Samanta  
Your Phone: 111-111-1111  
Email: testemail@test.com
user@user:~> 

The command has used regex matching i.e. a colon(':') followed by a space and then all non space characters.However, if the data contains any space character then this command will fail miserably.

Answer 2

This looks like an XY Problem - you say you want to add a break between every two words, but what you really want to do is pretty-print the data returned by an SQL query.

Your problem is caused by the fact that your executeSQLQuery function (or script or program) returns formatted output rather than just the data....and it looks like it is doing that because your $QUERY string is telling it to.

Can you get executeSQLQuery or a related function/script to return just the data in an easily parsed format (like CSV or tab-delimited), without column headers?

(in other words, "Don't Do That, Then". Separate the presentation of the results from gathering the data)

If that is possible, you could then use printf to format the data however you like. e.g. using tab-delimited output and a bash array:

QUERY="SELECT FullName, Phone, Email FROM Table1 WHERE EmpID=001;"

# disable globbing which would otherwise be performed upon non-quoted
# command substitution (except in zsh):
set -f

# set the input-field-separator to tab and newline (but not space characters)
# execute the query and store the results in the array $output
IFS=$'\t\n' output=($(executeSQLQuery "$QUERY"))

# now print the output array in the format we want.
printf "Your Name: %s\nYour Phone: %s\nEmail: %s\n" "${output[@]}"

---

Here's a working example using mysql. I don't know what your executeSQLQuery is, but I just made a test mysql database (called 'junk'), populated it with your example data, and defined executeSQLQuery as a function like so:

$ executeSQLQuery() { mysql --batch --silent junk -e "$@" ;}

Note the --batch and --silent options. They tell mysql to give me just the data without headers or other formatting. I haven't used the -u and -p options for user and password because I created a ~/.my.cnf file with my credentials in it.

Now, let's run the query, show what the ouput array contains, and then pretty-print it with printf.

$ set -f; IFS=$'\t\n' output=($(executeSQLQuery 'Select Fullname,Phone, Email from Table1 where EmpId = 001'))

$ set | grep output
output=([0]="Samantha" [1]="111-111-1111" [2]="testemail@test.com")

$ printf "Your Name: %s\nYour Phone: %s\nEmail: %s\n" "${output[@]}"
Your Name: Samantha
Your Phone: 111-111-1111
Email: testemail@test.com

BTW, bash doesn't support multi-dimensional arrays, so if your query returns multiple rows, you'd have to write some kind of loop to print [0..2], [3..5], [6..8] and so on as separate records. For example, I added a second record to Table 1, and changed the query to return all rows....here's what the output array looks like now:

$ set -f; IFS=$'\t\n' output=($(executeSQLQuery 'Select Fullname,Phone, Email from Table1'))
$ set | grep output
output=([0]="Samantha" [1]="111-111-1111" [2]="testemail@test.com" [3]="Fred" [4]="222-222-2222" [5]="testemail2@test.com")

Overall, though, it's much easier to do database work in Perl with the DBI library. If you're not committed to working in bash for some reason, I highly recommend starting to learn perl.

Answer 3

The problem with the output is that you would like to group three words (though logically key-value pair) in the first line of output, another three in the next and final two in the third line.

For this particular problem, the fast way would be:

executeSQLQuery "$QUERY" | awk '{print $1 " " $2 " " $3 "\n" $4 " " $5 " " $6 "\n" $7 " "$8 }'

But generally it would be better changing the query to something like:

SELECT FullName as 'Your name', Phone as 'Your phone', Email FROM Table1 WHERE EmpID=001;

and then getting the row along with column names and creating the desired output in perl or some other language.