Skip quoted string in regex

[roho]

I have the following code which splits a SQL clause into field, operator, and value using a regular expression of alternations composed of known operators. It works fine unless the quoted value in the SQL clause contains an operator character (the equal sign in my example), in which case $val contains 'Response.

I tried adding the usual pattern for quoted strings ("[^"]+") to the front of the regex alternations but that does not help. I tried adding the limit value of ",3" to split but that also does not help. How can I modify my regular expression (variable $opr_regex) to skip quoted values when splitting the SQL clause?

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

my $clause = qq(failure_reason <> 'Response=X');
my @operators = qw ( <> != !< !> <= >= < > = );
my $opr_regex = '(\s*' . join('\s*|\s*', @operators) . '\s*)';
my ($fld,$opr,$val) = split /$opr_regex/, $clause, 3;

print "\n\$opr_regex =  $opr_regex\n\n";
print "\$clause    =  $clause\n\n";
print "\$fld       = |$fld|\n";
print "\$opr       = |$opr|\n";
print "\$val       = |$val|\n\n";

[LanX]

Hi Roho

Usually it's discouraged to solve this kind of parsing with regex, but expecting that split could do this is even more than ambitious.

You would need look-arounds b/c both LHS and RHS could contain "operators".

Did you consider using something like SQL::Parser ?

Anyway the right approach¹ (IMHO) is to successively build a parse regex which starts from the left, by combining smaller parts like $clause = $lhs . $op . $rhs;

Like this you can also follow the official SQL grammar.

Saying this, there are already some BNF parsers available for Perl.

Good luck reinventing the wheel. ;-)

update

¹) not elaborating on the necessity to be able to recursively descent into nested structures like parens-groups and even sub-queries

[Anonymous Monk]

Thanks Rolf. I realized this as soon as I inherited this code. Due to constraints (time, $$$) I am trying to avoid a massive refactor (or rewrite), but alas, I may have to make the case to the powers that be that this will be more cost effective and beneficial in the long run. I will check out SQL::Parser. Thanks again.

[roho]

Sorry, I was not logged in. Thanks again.

[AnomalousMonk]

Like LanX, I have some reservations about your basic approach, but that said, just using a split limit of 2 seems to do the trick here:

c:\@Work\Perl\monks>perl -wMstrict -e
"my $clause = qq(failure_reason <> 'Response=X');
 my @operators = qw ( <> != !< !> <= >= < > = );
 my $opr_regex = '(\s*' . join('\s*|\s*', @operators) . '\s*)';
 my ($fld,$opr,$val) = split /$opr_regex/, $clause, 2;
 print \"\n\$opr_regex =  $opr_regex\n\n\";
 print \"\$clause    =  $clause\n\n\";
 print \"\$fld       = ^|$fld^|\n\";
 print \"\$opr       = ^|$opr^|\n\";
 print \"\$val       = ^|$val^|\n\n\";
"

$opr_regex =  (\s*<>\s*|\s*!=\s*|\s*!<\s*|\s*!>\s*|\s*<=\s*|\s*>=\s*|\
+s*<\s*|\s*>\s*|\s*=\s*)

$clause    =  failure_reason <> 'Response=X'

$fld       = |failure_reason|
$opr       = | <> |
$val       = |'Response=X'|

(Update: In the  qq(failure_reason <> 'Response=X') example given, you only actually want to split once.)

[LanX]

Some background...

> In the qq(failure_reason <> 'Response=X') example given, you only actually want to split once.)

I had the same idea but 3 seemed right, since 3 fields are returned.

It's a bit confusing, b/c according to the docs "LIMIT... represents the maximum number of fields the EXPR will be split into".

But the middle field originates from the (regex-group) within the $opr_regex.

But still, split is the wrong approach here, b/c the LHS could be quoted too.

[AnomalousMonk]

... the LHS could be quoted too.

I missed that bit in your reply. I was thinking of responding with a semi-elaborate regex approach, but if there's an SQL parser available on CPAN, I agree that's the way to go: wheel, reinvention, and all that.