WHERE IN with column content

Question

I got the following statement:

SELECT XYZ FROM ABC WHERE ID IN (123)

At the moment I made the 123 as a configuration parameter in a separate table:

SELECT XYZ FROM ABC WHERE ID IN (SELECT CONTENT FROM c_configuration WHERE IDENTIFIER = "ID")

Now the content from the c_configuration parameter changed to "123, 456". Is there any better way then splitting the field content at "," and insert them into an array and put the array in the WHERE ID IN ( ARRAY ) part?

Thank you in advance Matt

http://stackoverflow.com/questions/15515772/array-in-in-clause-oracle-plsql — Mihai, Sep 04 '14 at 07:07

John Woo · Accepted Answer · 2014-09-04T07:12:45.073

3

try out the following code:

SELECT XYZ 
FROM ABC 
WHERE ID IN 
(
  SELECT DISTINCT TO_NUMBER(REGEXP_SUBSTR(CONTENT,'[^,]+', 1, level))
  FROM c_configuration
  WHERE IDENTIFIER = 'ID'
  CONNECT BY REGEXP_SUBSTR(CONTENT, '[^,]+', 1, level) IS NOT NULL
)

SQLFiddle Demo

edited Sep 04 '14 at 07:12

answered Sep 04 '14 at 07:06

John Woo

258,903
69
498
492

Wow, thats pretty nice and smoooth. Thank you – frgtv10 Sep 04 '14 at 08:10

score 1 · Answer 2 · answered Sep 04 '14 at 07:03

1

You'll have to split up the csv value or use dynamic sql.

answered Sep 04 '14 at 07:03

sam yi

4,806
1
29
40

score 1 · Answer 3 · answered Sep 04 '14 at 07:21

you can create a function like this:

create function dbo.fn_SplitString 
(
    @str nvarchar(max), 
    @separator char(1)
)
returns table
AS
return (
with tokens(p, a, b) AS (
    select 
        cast(1 as bigint), 
        cast(1 as bigint), 
        charindex(@separator, @str)
    union all
    select
        p + 1, 
        b + 1, 
        charindex(@separator, @str, b + 1)
    from tokens
    where b > 0
)
select
    substring(
        @str, 
        a, 
        case when b > 0 then b-a ELSE LEN(@str) end) 
    AS id
from tokens
);

This function returns a "array/set" where you can use in your 'where in' statement

select * 
from myTable 
where ID in (
    select id 
    from fn_fn_SplitString(select CONTENT from c_configuration where IDENTIFIER = "ID",','))

WHERE IN with column content

3 Answers3