perl newbie trying to get just one word

[galorin]

I am migrating an Excel spreadsheet into MySQL, and as a part of that, I need to get out a string which is an invoice reference from comment fields. The following gets the info out of mysql and prints out matching lines. I don't know enough about perl yet to get out the string that I want. I'll show some sample output below, showing what I want, and what I don't.

$sel = "select Bill_ID, OurRef, YourRef, CostAccepted, DateSent, PONumber, GlulamValue, Comment  from Book1"; 

$db_bdb= DBI->connect('dbi:mysql:database=contacts;host=localhost:3306', $user, $pw, { RaiseError => 1, AutoCommit => 1 });

$rows = $db_bdb->selectall_arrayref($sel);

foreach $row(@$rows){

#misc code goes here for doing stuff with all the other data 

 if (@$row[7] =~ m/DES16-/ ) {
  $invoice = @$row[7];
  print "$invoice\n";
  }
}

Sample output looks like this

DES16-184
DES16-183jm
DES16-183jm
[COLOR=red]Inv no. DES16-183jm[/color] <- do not want Inv no.
DES16-146
DES16-146

I tried split() but couldn't get my pattern matching to work, as well as a few other ideas, but I just plain don't have enough experience with perl to get it right. Any suggestions would be appreciated.

 

[mikedaruke]

$invoice =~ s/Inv no\.//gsi;

 

[dmazzini]

if (@$row[7] =~ m/^DES16-/) 
[code] 

dmazzini
GSM/UMTS System and Telecomm Consultant

 

[brigmar]

You want to capture the string.

if (@$row[7] =~ m/(DES16-.*)/ ) {
  $invoice = $1;
  print "$invoice\n";
}

The parentheses capture to enclosed match and save it to $1.
Second and subsequent parentheses match to $2, $3, etc.

 

[KevinADC]

I don't think you can use a regexp on an array:

@$row[7] =~ m/(DES16-.*)

------------------------------------------
- Kevin, perl coder unexceptional!

 

[kordaff]

True, but that is the difference between these snippets:

@a=(1,2,3);
if ( @a =~ /1/ ) {print "yes!!!\n"}
else {print "oh noes!!!\n"}

$a=[1,2,3];
if ( @$a[0] =~ /1/ ) {print "yes!!!\n"}
else {print "oh noes!!!\n"}

@a is the array, $a is the array ref. OP is missing ^ in regex...

Kordaff

 

[galorin]

With some of the comments here and the aid of a colleague I came up with the following which does the job well.

foreach $row(@$rows){

 if (@$row[7] =~ m/(DES16-)(\d{1,3})(\p{IsAlpha}{0,2})/ ) {
  $invoice = "$1$2$3";
  } else {
  $invoice = "";
  }

  $sql = "insert into Billing (YourRef,Quote_ID,DateSent,PONumber,Comment,GlulamValue,InvoiceNo) values ('@$row[2]','@$row[1]','@$row[4]','@$row[5]','@$row[7]','@$row[6]','$invoice');";
 print file_to_write "$sql\n";
}

If yall can see anything better than above, let me know. Thanks.