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.

---

---

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! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

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:
	&		&
	<		&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.