修剪数组（通过DBI填充）

I'm trying to read data from SQL Server database using Perl and the DBI module. My intention is to read the data and print it into a text file (comma separated). When I do this, I get the result like this:

我正在尝试使用Perl和DBI模块从SQL Server数据库中读取数据。我的目的是读取数据并将其打印到文本文件(逗号分隔)。当我这样做时,我得到如下结果:

var1,var2,var3
40406,20 ,783
50230,78 ,680
50230,78 ,680
50230,78 ,680
50230,78 ,680

So there is a whitespace between the second variable data and the comma. I tried to trim this using the code below, but it did not work. How should I modify my code to get rid of those whitespaces?

所以第二个变量数据和逗号之间有一个空格。我尝试使用下面的代码修剪它,但它没有用。我应该如何修改我的代码以摆脱那些空白?

My code is here:

我的代码在这里:

#!/bin/perl
use warnings;
use strict;
use DBI;

sub trim;

my $dbs = "dbi:ODBC:DRIVER={SQL Server};SERVER={xxxx}";
my ($username, $password) = ('un', 'pwd');

my $dbh = DBI->connect($dbs, $username, $password)
               or die "Can't connect to $dbs: $DBI::errstr";

my $sth = $dbh->prepare("select var1, var2, var3 from db.dbo.table")
                or die "Can't prepare statement: $DBI::errstr";

$sth->execute();

my $outfile = 'temp.txt';
open OUTFILE, '>', $outfile or die "Unable to open $outfile: $!";

print OUTFILE join(",", @{$sth->{NAME}}), "\n";

while (my @re = $sth->fetchrow_array) {
   print OUTFILE join(",", trim(@re)), "\n";
}

close OUTFILE;

$sth->finish();
$dbh->disconnect();

############## subroutines ##################
sub trim($) {
    my $string = shift;
    $string =~ s/^\s+//;
    $string =~ s/\s+$//;
    return $string;
}

3 个解决方案

#1

Your trim() function does not modify the list in place (nor it handles a list).

trim()函数不会修改列表(也不处理列表)。

So, in real TIMTOWTDI fashion, you should either modify the function to return a new array:

因此,在实际的TIMTOWTDI方式中,您应该修改函数以返回一个新数组:

sub trimArray {
    my @arr = @_;
    my @rv;
    for my $val (@arr) {
        $val =~ s/^\s+//;
        $val =~ s/\s+$//;
        push @rv, $val;
    }
    return @rv;
}

#and then

print OUTFILE join(",", trimArray(@re)), "\n";

or pass a reference to your function and then modify the array in place

或者传递对函数的引用,然后修改数组

sub trimInPlace {
    my $arrRef = shift;
    for my $val (@$arrRef) {
        $val =~ s/^\s+//;
        $val =~ s/\s+$//;
    }
}

#and then

trimInPlace(\@re); #Note the \
print OUTFILE join(",", @re), "\n";

or use map

或使用地图

#!/bin/perl
use warnings;
use strict;
use DBI;

#... the same

while (my @re = $sth->fetchrow_array) {
   print OUTFILE join(",", map { trim($_); } @re), "\n"; #Applies
                                                         #trim() to each element
}

#...

############## subroutines ##################
sub trim { #Don't use prototypes
    my $string = shift;
    $string =~ s/^\s+//;
    $string =~ s/\s+$//;
    return $string;
}

or try using chomp, by modifying $/, which will only remove a trailing space, nothing more.

或尝试使用chomp,通过修改$ /,这将只删除一个尾随空格,仅此而已。

#!/bin/perl
use warnings;
use strict;
use DBI;

#... the same

my $old_sep = $/;
$/ = " ";
while (my @re = $sth->fetchrow_array) {
   chomp(@re); #Modifies in place, returning number of changes
   print OUTFILE join(",", @re), "\n";
}
$/ = $old_sep;

#2

You could also check to see if DBD::ODBC supports the ChopBlanks attribute:

您还可以检查DBD :: ODBC是否支持ChopBlanks属性:

my $dbh = DBI->connect($dbs, $username, $password, { ChopBlanks => 1 } )

the ChopBlanks attribute trims the trailing whitespace of any CHAR fields (that is if your driver supports it ... I'm not sure if DBD::ODBC does).

ChopBlanks属性修剪任何CHAR字段的尾随空格(即如果你的驱动程序支持它......我不确定DBD :: ODBC是否支持)。

#3

Why does that field have trailing whitespace? Usually that points to some sort of problem with the database model. Besides your trim() function, you might investigate why the data is dirty.

为什么那个字段有空格?通常这表明数据库模型存在某种问题。除了trim()函数,您可以调查数据为何脏。

#1