SQL and Perl comparison

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

Dear Monks,

Please could people point out the differences between the following:

#! perl -w script

use strict;
use warnings;


my $Method; 
my $number_of_years;

$Method = 'DDD_t_s_2222_2222_01_2222';


if $Method =~ /DDD_____%/ {

     $number_of_years = ($yearto-$yearfrom)+1; 

}
elsif ($Method =~ /DDD_t(.|\d){13}(\d{2})/) {
        

  if ($Method =~ /DDD_t(.|\d){14}(\d{1})/) {

    $number_of_years = $2;

  }
  elsif ($Method =~ /DDD_t(.|\d){13}(\d{2})/) {

    $number_of_years = $2;

  }
}
[download]

and


DECLARE @Method AS NVARCHAR
SET  @Method = 'DDD_t_s_2222_2222_01_2222'

DECLARE @number_of_years AS NVARCHAR


if @Method LIKE 'DDD_____%' begin

    SET @number_of_years = (@yearto-@yearfrom)+1 

end
else if @Method LIKE 'DDD_t%' begin

    if (SELECT SUBSTRING(@Method, 21, 1)) = 0 begin
        
        SET @number_of_years = (SELECT SUBSTRING(@Method, 22, 1))     
+ end
    else begin
        
        SET @number_of_years = (SELECT SUBSTRING(@Method, 21, 2))
    end
end
[download]

As at least one of them does not behave as expected.

Comment on SQL and Perl comparison Select or Download Code

Replies are listed 'Best First'.
Re: SQL and Perl comparison by pg (Canon) on Oct 27, 2005 at 11:37 UTC
One of the problem is your use of % in Perl. In your SQL example, % is a wild card, but not in Perl though. The Perl version does not seem to be correct. Get rid of that % in the regexp. SQL does a head-to-tail match (it matches the entire string) and that is why you need that % at the end. But Perl regexp does not work in that way, it can match a portion of the string. For example, SQL 'like abc%' is close to Perl's '/^abc.*$/. But in your case, if DDD always appears at the beginning, there is no need to use ^ or $. Your second regexp could be as simple as /DDD_t/, or /^DDD_t/. For your last two regexp's, you can use substr() just like the SQL version does.	[reply]
Re^2: SQL and Perl comparison by dragonchild (Archbishop) on Oct 27, 2005 at 12:11 UTC
In addition, '_' is the SQL equivalent of '.' in Perl regexes. So, 'DDD______%' is equivalent to 'DDD.{6}.' in Perl. (Note that the two-character token "." is equivalent to '%' in SQL, not the one-character token '*'.) My criteria for good software: Does it work? Can someone else come in, make a change, and be reasonably certain no bugs were introduced?	[reply]
Re^2: SQL and Perl comparison by Win (Novice) on Oct 28, 2005 at 09:20 UTC
There are problems with the SQL as well.	[reply]