BASH: get data from MySQL where records have spaces

Question

I'm trying to get values from a MySQL database, for a later substitution with perl, everything works fine but if a record contain space " " the variables will be set uncorrectly.

Example if in the database I have:

sede = "street"
fede = "calvin and hobbes"
lede = "12"

the result for the variables will be:

$TAGSEDE = "steet"
$TAGFEDE = "calvin"
$TAGLEDE = "and"

I understood there is something wrong in setting $DBDATAF but, I can't identify it (english isn't my mother language so some misunderstanding are more than a possibility).

DBDATAF=$(mysql -u$DBUSER -p$DBPASS -se "USE $DBNAME; SELECT sede, fede, lede FROM $DBTABL WHERE cf='$CFPI'")

read TAGSEDE TAGFEDE TAGLEDE <<< $DBDATAF



/usr/bin/perl -p -i -e "s/TAGDATAINSERT/$TAGDATAINSERT/g" $i

ikegami · Accepted Answer · 2014-04-30 17:54:00Z

2

Your code breaks down for two reasons:

When $DBDATAF is passed to the <<< operator, the tabs are discarded. To keep them, double quote the variable as shown below.
read separates the input line into words using the IFS special variable. By default, it separates on tab, space or newline. So even though tabs are now preseved when passed to <<<, the read command splits on spaces also. Setting IFS to a tab makes read split as desired. Putting the IFS assignment and read on one line ensures that IFS returns to the default after read exits

IFS="$( echo -e '\t' )" read TAGSEDE TAGFEDE TAGLEDE <<< "$DBDATAF"

edited Apr 30, 2014 at 17:54

answered Apr 30, 2014 at 17:30

ikegami

391k17 gold badges291 silver badges555 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

benrifkah Over a year ago

Explanation: The OPs code breaks down in the read call for 2 reasons: 1. When $DBDATAF is passed to the <<< operator tabs are discarded. To keep them double quote the variable as shown above. 2. read separates the input line into words using the IFS special variable which by default separates on tab, space or newline. So even though tabs are now preseved when passed to <<< the read command splits on spaces also. Setting IFS to only use tab characters gets around this. Finally, putting the IFS assignment and read on one line ensures that IFS returns to the default after read exits

ikegami Over a year ago

Thanks, @benrifkah. Incorporated your comment into my answer.

Can Che Dorme Over a year ago

Thanks guys, clear and smooth! using IFS reading one line is perfect. Works also with 20 variables to read.

Collectives™ on Stack Overflow

BASH: get data from MySQL where records have spaces

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related