I posted a while ago, but did not post sufficent data or a proper explanation of what I was doing. Sorry I'm new to this. We have a stand alone product which is close source and want to do some reporting on it which the software doesn't cater for. Rows within a database contain column value pairs, I need to use these values to build up a SQL query. I had started to develop a script using SQL::Abstrat, but it got bloated pretty quickly as there are many columns.

1. There are multiple COL* columns.
2. Columns may contain wildcards or pipes to designate logical or searches.
3. 'ADDITIONAL' column can contain multiple key value pairs.
4. Dates in the format yyyymmdd

Some example data:
TABNAME                COL1                ADDITIONAL                 
+                   
__________________________________
TableA                FOO              
TableA                F*              
TableA                FOO|BAR   
TableB                                        &COLNAME=VALUE
TableB                                        &COLNAME=V*
TableB                                        &COLNAME=VALUE&COL2=VALU
+E
TableB                                        &COLNAME=VALUE&COL2=VAL*
TableB                                        &COLNAME=VALUE|VALUE2&CO
+L2=VALUE
TableB                Foo|BAR            &COLNAME=VALUE|VALUE2&COL2=VA
+LUE
TableB                Foo                    &DATERAGE=19910101:199101
+31
TableB                Foo                    &DATERAGE=<19910101

[download]

I started by writing a script which processed one field at a time, checked for each condition and attempting to build the $where condition based on the results. Since the number of COL columns is actually quite large this copying and pasting approach resulted in a bloated script, very long. I did some reading in the tutorials section and found a note in 'subroutines' about higher order functions (http://perlmonks.com/?node_id=492651) and decided to investigate. I tried to start of a sub to call with each the contents of each COL and process that way. Example code is below, I've excluded the SQL to fetch the data, I have no problems there. I've yet to try the SQL abstract magic within this sub since I'm having some problems: 
#!/usr/bin/perl

use strict;
use warnings;

use DBI;
use Data::Dumper;
use SQL::Abstract;

# Subroutine to call

sub process(){

    my $processed = $_[0];
    
    return sub {
    
        if ( @_ ){
            #@_ =~ s/^&//; # strip leading & - this only occurs in the
+ 'additional field'
            print "recieved: @_\n";
                
            # detect additional with multiple key value pairs.

            if ( index(@_,'&') >= 0 ){
                print "@_ contaisn & character - so it's the additiona
+l field with multiple key value pairs";
            }
            
            # detect ors
            if ( index(@_,'|') >= 0 ){
                print "@_ contains pipe character for ORs";
            }

            # detect wildcards
            if ( index(@_,'*') >= 0 ){
                print "@_ contains whidcard character";
            }
        }
    
    }
    
}

# skip the actual fetching of the data via SQL query, I have no proble
+ms with that part

process->($TABNAME) if defined($TABNAME);
process->($COL1) if defined($COL1);
process->($ADDITIONAL) if defined($ADDITIONAL);

[download]

Questions.
1. Why can I print what it has recieved but not detect characters within it using index?
2. Does using a sub routine seem like a sensible approach?
3. I'm having difficulty solving the problem for the ADDITIONAL field where it could contain multiple key value pairs.
All constrictive advice appreciated.

In reply to Sub Routines and Building SQL statement by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.