Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks!
I am returning values from a db query but I need to get rid of the spaces before and at the end of every string result returned. I know by doing this will work but: 
...
foreach my $row (@$values) {
      $row->{'name'}=~s/^\s+|\s+$//g;
      $row->{'last'}=~s/^\s+|\s+$//g;
      ...
  }
...

[download]
I'm wondering if there is a more direct way other then on the SQL query itself like: 
@$values=~s/^\s+|\s+$//g;

[download]
because of the size of the table, I am trying to do it without looping or specifying each row name. Is it possible?
Thanks!

Replies are listed 'Best First'.
Re: Removing spaces from query results
by choroba (Cardinal) on Apr 30, 2013 at 14:08 UTC
    I would use LTRIM(RTRIM(...)) on the SQL side.
    لսႽ† ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ
[reply]
[d/l]
      I did but it doesn't work <code>..RTRIM(LTRIM(name)) as name..." I have to do it in the Perl.
[reply]
        Strange. Works for me: 
        #!/usr/bin/perl
use warnings;
use strict;

use DBI;

my $db = DBI->connect('dbi:SQLite:dbname=1.db', q(), q());
$db->do('CREATE TABLE T (name varchar)');

my $insert = $db->prepare('INSERT INTO T VALUES(?)');

for my $name (qw(Matthew Mark Luke John)) {
    $insert->execute((' ' x rand 10) . $name . (' ' x rand 10));
}

my $select = $db->prepare('SELECT LTRIM(RTRIM(name)) AS name FROM T');
$select->execute;
while (my $name = $select->fetchrow_arrayref) {
    print "|$name->[0]|\n";
}

        [download]

        Output: 

        |Matthew|
|Mark|
|Luke|
|John|

        [download]
        لսႽ† ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ
[reply]
[d/l]
[select]
Re: Removing spaces from query results
by Corion (Patriarch) on Apr 30, 2013 at 14:14 UTC

    I would modify the query appropriately.

    As a bad alternative, consider the values keyword:

    for my $row (@$values) {
    for( values %$row) {
        ...
    };
};

    [download]
[reply]
[d/l]
Re: Removing spaces from query results
by kcott (Archbishop) on Apr 30, 2013 at 19:21 UTC

    If your keys don't have leading or trailing whitespace, you could filter your results like this: 

    $ perl -Mstrict -Mwarnings -E '
    my $values = [
        { name => "  qwe  ", last => "   rty" },
        { name => "asd   ", last => "   fgh   " },
        { name => "   zxc", last => "vbn   " },
    ];
    say "Raw:";
    say "@{[%$_]}" for @$values;
    say "Filtered:";
    say "@{[%$_]}" for map { { map { /^\s*(.*?)\s*$/; $1 } %$_ } } @$v
+alues;
'
Raw:
last    rty name   qwe  
last    fgh    name asd   
last vbn    name    zxc
Filtered:
name qwe last rty
name asd last fgh
name zxc last vbn

    [download]

    Update: Originally, the last line of the code above was: 

    say "@{[%$_]}" for map { { map { /^\s*(.*?)\s*$/; $1 } @{[%$_]} } } @$
+values;

    [download]

    I wanted the @{[%$_]} construct in the double-quoted string to format the output. I repeated this construct in the map but it's unnecessary: just %$_ (as it is now) is fine.

    -- Ken

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom