## Varying IN Lists

### Integer Series Generator Method

With a little more code than the previous solution we can write a pure SQL query that implements a varying IN list without supressing indexes. The key is to use logic that converts a string to a set of rows. Fortunately we have a number of these techniques in our arsenal at SQL Techniques Tutorials: String to Rows. In the next example we employ a method described at String to Rows: Integer Series Generator Method.

```variable d varchar2

execute :d := ','

variable p varchar2(100)

execute :p := '1,3,5'

select *
from   t
where  key in
( select
to_number
( substr
( p,
instr( p, :d, 1, i.position ) + 1,
instr( p, :d, 1, i.position + 1 )
- ( instr( p, :d, 1, i.position ) + 1 )
)
) as val
from
( select
column_value   as position ,
:d || :p || :d as p
from
table( integer_varray_type( 1,2,3,4,5,6,7,8,9,10 ) )
where
column_value <= length( :p || :d ) - nvl( length( replace( :p, :d, null ) ), 0 )
) i
)
order by 1 ;
```
```       KEY C
---------- ----------
1 v1
3 v3
5 v5
```

Duplicates in the IN list produce the same result as if there were no duplicates.

```execute :p := '1,3,3,3,3,5'

/
```
```       KEY C
---------- ----------
1 v1
3 v3
5 v5
```

The query accepts IN lists with spaces.

```execute :p := '1, 2, 3, 4, 5'

/
```
```       KEY C
---------- ----------
1 v1
2 v2
3 v3
4 v4
5 v5
```

If the IN list is null no rows are returned.

```execute :p := null

PL/SQL procedure successfully completed.

/

no rows selected

```

For quoted string lists like 'v1', 'v3', 'v5' we need to tweak the logic slightly.

```
-- the delimiter is a single quote this time

execute :d := ''''

execute :p := q'['v1', 'v3', 'v5']'

select *
from   t
WHERE  C IN
( select
substr
( p,
instr( p, :d, 1, i.position ) + 1,
instr( p, :d, 1, i.position + 1 )
- ( instr( p, :d, 1, i.position ) + 1 )
)
as val
from
( select
column_value as position ,
:P AS P
from
table( INTEGER_VARRAY_TYPE( 1,3,5,7,9,11,13,15,17,19 ) )
where
column_value < NVL(LENGTH(:P),1) - NVL( LENGTH( REPLACE( :P, :D, NULL ) ), 0 )
) i
)
order by 1 ;
```
```       KEY C
---------- ----------
1 v1
3 v3
5 v5
```

#### Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

• [url=http://www.sqlsnippets.com/en/topic-12443.html]SQL Snippets: Varying IN Lists - Integer Series Generator Method[/url]