First, you need to figure out what you would convert each case of your criteria to, and then figure out what the common elements are. I'm going to assume a simple text field. So the first one is pretty simple, you want to convert FOO to column_name = 'FOO'. The second one is also pretty simple, you can convert it to:

column_name in ('FOO', 'BAR', 'BAZ')
[download]

or you could use:

(column_name = 'FOO' or column_name = 'BAR' or column_name = 'BAZ')
[download]

For the third case, you'd want to convert it to something like:

(column_name like 'FO%' or column_name like 'B%' or column_name = 'CAT
+')
[download]

To make things simpler, we should wrap all of our clauses in parenthesis, so we don't have to add code to figure out when we need them or not. You need them in some cases to allow different clauses to interact properly, as we wouldn't want an adjacent search clause to interact with part of this one.

So to code it up, I'd suggest something like:

sub generate_criteria {
   my ($ColName, $Criteria) = @_;
   my @fields = split /\|/, $Criteria;

   return " $ColName = '$field[0]' " if @fields==1;

   my @ret;
   my @wildfields = grep { $_ =~ /[%_]/ } @fields;
   if (@wildfields) {
      push @ret, join(" or ", map { "$ColName like '$_'" } @wildfields
+;
   }
   my @constfields = grep { $_ !~ /[%_]/ } @fields;
   if (@constfields) {
      push @ret, " $ColName in (" . join(", ", map { "'$_'" } @constfi
+elds) . ")";
   }
   return join(" ", "(", @ret, ")");
}
[download]

Now there are a few details you'll want to take care of, like ensuring the fields are properly quoted, etc., but this is how I'd approach the problem. Let me know if you need any further details or explanations.

...roboticus

When your only tool is a hammer, all problems look like your thumb.

Update:

1. I didn't test any of the code, so there may be some errors here and there. (I code & type fast, and let perl tell me when I bobbled the syntax.)
2. I didn't think of it at the time, but we can take care of the quotes with another map statement just after the initial split, like so:
   

   my @fields = map { s/'/''/g; $_ } split /\|/, $Criteria;
   [download]

3. Finally, I know I could've gotten away with a simpler routine by treating all cases the same:
   

   sub generate_criteria {
      my ($ColName, $Criteria) = @_;
      my @fields = map { s/'/''/g; $_ } split /\|/, $Criteria;
   
      return "( " . join(" or ", map { "$ColName LIKE '$_'" } @fields) . 
   +" )";
   }
   [download]

   It works because LIKE used with a constant string is converted to the same code as '=' (at least it appears so in Oracle, Sybase and MS SQL). But I like making the SQL look like it would if I generated it by hand.