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

use Test::More;

sub simple_value {
    my ($name, $val) = @_;
    my $return;
    if (not length $val) {
        $return = q();
    } elsif (1 + index $val, '|') {
        my @vals = split /\|/, $val;
        $return = "$name in(" . join(',' => map "'$_'", @vals) . ")";
    } elsif (1 + index $val, '%') {
        $return = "$name like '$val'";
    } else {
        return "$name='$val'";
    }
}


sub complex_value {
    my $value = shift;
    my @simple = split /&/, $value;
    return glue(map {
        my ($name, $val) = split /=/;
        simple_value(lc $name, $val);
    } @simple);
}


sub glue {
    join ' and ', grep length, @_;
}


while(not eof DATA) {
    chomp(my ($type, $stype, $other, $result) = (map scalar <DATA>, 1 
+.. 5));
    $type  = simple_value('type',  $type);
    $stype = simple_value('stype', $stype);
    $other = complex_value($other);
    is(glue($type, $stype, $other), $result, $result);
}
done_testing();


__DATA__
AAA
BBB
X=CCC
type='AAA' and stype='BBB' and x='CCC'

AA|BB
BB%
X=CC%&Y=DDD
type in('AA','BB') and stype like 'BB%' and x like 'CC%' and y='DDD'

AA|BB

X=CC%&Y=DDD
type in('AA','BB') and x like 'CC%' and y='DDD'

AA
BB%
X=CC%&Y=DD%
type='AA' and stype like 'BB%' and x like 'CC%' and y like 'DD%'
[download]

Thank you for this reply. I will run this with some production data and report back. Thanks again. Have you any experience of the SQL::Abstract module? I'm looking at it right now and it claims to "Generate SQL from Perl data structures". Thank you once more.

To me, the problem looks more like an issue with your database schema design.

If you normalised out the "other" column into its own key/value table then your problem would go away and your code would be nice and simple :)

Indeed. And you wouldn't be trying to execute data, which is something to be avoided.

I should have been more clear, this isn't our schema, these aren't our tables. This is generated by a third party product (VB.Net front end, Oracle back end) over which we have zero control. We're trying to provide more functionality via some scripting. We want to do things this product doesn't do.

If the amount of data is at-all reasonable, you could write a script which populates additional SQL tables whose purpose is to index the data that you receive from this most-peculiar application.   Create a suitable primary-key column even if you have to do it by hand, then scan through the data inserting rows in other tables that consist of break-downs of what is in the original data.   For example, two entries in an index-table for row #1 would specify that it contains 'AA' and 'BB'.   Once you have done this, now you can begin to do sensible queries against the data that you get.

A script to parse the original data stem-to-stern, inserting descriptive records in other tables, would be fairly easy to write and could accomplish its work with a single pass through the data.   Now, you no longer have to depend upon its weirdnesses.   I truly believe that you will never get a truly-satisfactory program from the approach that you are pursuing right now.   I daresay that the incoming data is full of special-cases and exceptions that would vex you endlessly.

Not a SQL question, see http://perlmonks.com/?node_id=1019598 for more detail.