CODEWITHSUNDEEP
sqlbash
Philippe CARLE
Philippe CARLE

Reputation: 571

Shell - Looping through sql result

I'm deseperatly trying to loop through sql query results… It's working quite well except that vars with spaces in strings make me weird output… like if they were considered as arrays or something like that… any idea ??

echo "SELECT shop_id, shop_address1, shop_address2, shop_zip, shop_city FROM base.table" | mysql -h hostname -u user -ppassword | while read -r shop_id shop_address1 shop_address2 shop_zip shop_city;
do
        echo $shop_address1
done

Upvotes: 0

Views: 2685

Answers (2)

arutaku
arutaku

Reputation: 6087

For this kind of tasks I use AWK.

For example, if you want to get the first and the third columns:

mysql -u <USER> -p<PASSWORD> -h <HOST> < <QUERY_FILE> | awk -F"\t" '{print $1","$3;}' -

$X is the way to acces to the Xth column, it is like "array notation".

Obviously you can do more complex taks using AWK ;-)

Upvotes: 1

Raul Andres
Raul Andres

Reputation: 3806

Use OUTFILE to extract to csv

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;

Then use cut and delimiter to access fields

Upvotes: 0

Related Questions