Mark C. Stock wrote:
> "Angrydot" <spam_at_spamme.com> wrote in message
> news:w9-dnXBQ0N7UarPfRVn-uA_at_comcast.com...
>
>>Greetings,
>>
>>I have a text variable A = 't1:t2:t3' that I would like to use in the SQL
>>IN clause (i.e. SELECT * FROM X WHERE c IN (A);).
>>
>>I tried REPLACE and TRANSFORM with no success. For example IN
>>(REPLACE(a,':',''',''')).
>>
>>Is there a way to do this directly in the SQL statement and not using
>>PL/SQL or VB?
>>
>>TIA
>
>
> for SQL in general, the IN operator expects to work on a list ... either an
> explicit list of expressions, or a list returned by a subquery
>
> so, for any RDBMS, and any version, you cannot use a single scalar variable
> that has a delimited list embedded in it and expect the IN operator to parse
> out its elements
>
> so, you have to transform the scalar to a table using a custom function
> (quite doable in Oracle, from, IIRC, 8i on -- using a piplined table
> function) or use a different approach
>
> without writing a special function to transform the scalar variable, you
> could try a syntax like
>
> WHERE instr( ':'|| :A || ':' , ':' || c || ':) > 0
>
> which wraps your variable A in semicolons -- ie. it becomes ':t1:t2:t3:' --
> and then checks if the value of column c, also wrapped in semi-coluns,
> occurs in the string
>
> so for a c value of 't1', the INSTR function will check if ':t1:' occurs in
> ':t1:t2:t3:', and if it does, return its position (hence, the >0 check)
>
> there are limitations to this technique -- the separator character (':')
> cannot occur in the data, and performance will be an issue
>
> ++ mcs
>
>
Thank you. I will test the performance of INSTR and a separate function
and report back.
Received on Thu Mar 10 2005 - 05:40:30 CST
