Re: Transform text for IN clause?

"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 Received on Wed Mar 09 2005 - 17:57:15 CST