sybperl-l Archive

From: jander at lehman dot com (Jim Anderson)
Subject: Re: Framing search sql's
Date: Feb 2 1996 1:29PM

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

And to "Randalize" the invocation of Gisle's routine:

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


-- 
Jim Anderson					Phone:  (201)524-4076
Lehman Brothers, Inc.				Fax:	(201)524-5153 
101 Hudson Street, 34th Floor			E-mail: jander@lehman.com
Jersey City, NJ 07302					jander@jander.com