Mysql output format in Bash Script

output of mysql is tab delimited. column replaces tabs with spaces breaking your input.

#!/bin/bash

while read -r output;
do
    # use double quotes with "$output" to avoid conversion
    # of tabs to spaces and set awk's Field Separator to "\t"
    echo "$output" | awk -F"\t" '{print $4}'
    #do something
done< <(mysql -u root -ptoor super_market -h 0 -e "select * from items;" | sed 1d)

test run:

root@c2:~# mysql -u root -ptoor super_market -h 0 -e "select * from items;"
+---------+------------+--------+-------------+-------+
| item_id | item_name  | weight | brand       | price |
+---------+------------+--------+-------------+-------+
|       1 | Milk       |      2 | Nestle      |     2 |
|       2 | Cheese     |    2.5 | Amul        |     6 |
|       3 | Chips      |     25 | Lays        |     3 |
|       4 | Coke       |      5 | Coke Cola   |   3.5 |
|       5 | Engage     |      5 | Deo         |   3.5 |
|       6 | Engage     |      5 | Deo         |   3.5 |
|       7 | Ear phones |      4 | Skull Candy |  32.3 |
+---------+------------+--------+-------------+-------+
root@c2:~# ./test.sh 
Nestle
Amul
Lays
Coke Cola
Deo
Deo
Skull Candy
root@c2:~# 

You don't have to use column tool as your table is really done. Try using awk with the common delimiter |.

You can do the following in your echo line

echo "$output" | awk -F'|' '{print $5}'

So it ends as

#!/bin/bash

while read -r output;
do
    echo $output | awk '{print $5}'
    #do something
    done< <(mysql -u root -ptoor super_market -h 0 -e "select * from items;" | sed '1,2d')

This is a very dirty and hacky solution, but you can use the dot (.) as the separator since you have floating point numbers on each side. You can call awk as follows

awk -F '.' '{a=gensub(/^[0-9]*\s*|\s*[0-9]*$/,"","g",$2); print a}'

in the script and it shall print only the brand.

There will be extraneous spaces in the middle of the brand names but you can extend the awk script to cope with that as well:

awk -F '.' '{a=gensub(/^[0-9]*\s*|\s*[0-9]*$/,"","g",$2); b=gensub(/\s+/," ","g",a); print b}'

awk's gensub function is a very powerful tool for such kinds of problems. Although this specific example is dirty, i believe, that knowing how to use gensub (the manual is quite decent) will allow you to solve similar problems with ease.

The above is GNU awk (gawk) specific. Old school unix systems (AIX, HP-UX) have no gawk. Funny enough, for some unknown reason, debian also has no gawk by default. debian has mawk as the default awk installed. Here is a mawk version:

awk -F '.' '{gsub(/^[0-9]*\s*|\s*[0-9]*$/,"",$2); gsub(/\s+/," ",$2); print $2}'

mawk has not gensub, it only has gsub (global substitution). gsub is vastly inferior to gensub because it does not return its substitution. If you are using debian and need awk often i strongly recommend installing gawk (it is in the main repo).

Use mysql with the -t flag, to get the output you get from a query in mysql directly as your bash output.

Afterwards you can use sed or awk to clean up the delimiters, to remove the horizontal lines and the |'s.