SQL IN Clause Splitter

How to workaround limit of IN Clause in Oracle
ORA-01795: maximum number of expressions in a list is 1000

Simply enter your values and we will split it into multiple IN Clauses with less than or equal to 1000 values each and glue them by ORs.

We will also remove any duplicates and sort the values for you.






Welcome to SQL IN Clause Splitter

If you've encountered the error ORA-01795: maximum number of expressions in a list is 1000, we can help you. You receive this error when you attempt to use more than 1000 expressions or literals in the IN clause in Oracle.

SELECT * 
FROM table
WHERE column IN ('column_value_1', 'column_value_2', ..., 'column_value_1100');
SQL IN Clause Splitter is a very handy tool which can split your values into multiple IN Clauses with less than or equal to 1000 values each and glue them by ORs. It also removes any duplicates in the input values and sorts them for you.

What is ORA-01795?

ORA-01795: maximum number of expressions in a list is 1000
Cause: Number of expressions in the query exceeded than 1000.
Note that unused column/expressions are also counted Maximum number of expressions that are allowed are 1000.
Action: Reduce the number of expressions in the list and resubmit.

The number of expressions or literals in the IN-clause is limited to 1000. You can read more about it on Oracle's website.

Solution

Temporary Table

So, what if you actually have a list of 1000+ values to compare? You can populate a temp table with the values and use a select on that table in the IN Clause.

SELECT * 
FROM table
WHERE column IN (SELECT column_value FROM temp_table);


Splitting

However, this is far too much effort when you are running adhoc on-demand queries. An easier workaround is to split up the list into 1000 item chunks across multiple INs and glue them using ORs.

SELECT * 
FROM table
WHERE column IN ('column_value_1', 'column_value_2', ..., 'column_value_1000')
OR column IN ('column_value_1001', 'column_value_1002', ..., 'column_value_1100');

How to use SQL IN Clause Splitter?

Input

Enter Column name: language
Choose whether the values are separated by New Line or Comma: New Line (default)


Powerhouse
Cython
Perl
Flex
Groovy
...more languages

Output

Voila! splitted IN Clauses:

(
language IN (
'Cython','Flex','Groovy'...,
... upto 1000 languages
)
OR language IN (
'Perl','Powerhouse',...
)
)