Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

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.

share|improve this question
1  
What is executeSQLQuery? What flavor of SQL? –  jordanm Sep 21 '12 at 23:53
add comment

3 Answers 3

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.

share|improve this answer
    
This seems to be the solution for my question. Thanks a lot! I will try this. –  user997452 Sep 24 '12 at 17:13
    
Hi Craig, I have tried this and when I echo it, it is working perfectly. I have stored it in a variable like this: <br /> printf -v myvariable $'Your Name: %s\nYour Phone: %s\n\Email: %s\n' "${output[@]}" . <br />It is printing as expected but when I try to echo(with -e option) the "myvariable" in the email that I am sending out, the formatting vanishes. –  user997452 Sep 24 '12 at 22:25
    
at a guess, try putting double-quotes " around "$myvariable" when you echo it - that will preserve any line feeds (otherwise the shell will strip them). Also, i don't think you want a backslash before the E in \Email. –  cas Sep 24 '12 at 23:31
    
Thanks but I have used <br /> tag instead of \n and it worked since the Content-Type is text/html. Thanks for all your help! Really appreciate the effort you put in. –  user997452 Sep 25 '12 at 4:07
add comment
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.

share|improve this answer
    
Yes, I have tried this before and it failed where the data has a space character. –  user997452 Sep 24 '12 at 17:12
    
why dont you do something like SELECT 'Your Name:'+'\t' with your Query....it will help as delimiter flag.. –  perilbrain Sep 24 '12 at 18:49
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.