sybperl-l Archive

From: Gisle Aas <aas at bergen dot sn dot no>
Subject: Re: Framing search sql's
Date: Feb 2 1996 9:38AM

In message <199602020218.VAA03557@swapsdvlp02.ny-swaps-develop.ml.com>, "Brent 
B. Powers Swaps Programmer x2293" writes:
> Shankar Kris writes:
>  > I maintain a list that has *data* on which to search for.
>  > @list = qw( 1 2 3 4 5);
>  > 
>  > I frame the 'sql' like this:
>  > 
>  > $"=',';   
>  > $sql = "select col1,col2 from table_name where col1 in (@list)";
>  > 
>  > This works fine when the search list involves integer data types.
>  > 
>  > While searching for char data types. since each element has to enclosed
>  > in quotes. I am unable to come up with a simple elegant way to frame the
>  > search 'sql'. Is there a good way to do this. else I might just loop
>  > thru eeach element and frame the sql.
>  > 
>  > Any suggestions.
>  > 
> $sql = "select col1, col2 from table_name where col1 in ('" . join("', '",@li
st) . "')";

I would make myself a function (so that we handle embeded "'" correctly):

  sub sql_list {
       my @list = @_;
       join(", ", map { s/'/''/g; "'$_'" } @list)
  }

  $sql = "select col1, col2 from table_name " .
         "where col1 in (" . sql_list(@list) . ")";

Regards
Gisle Aas