Alternative to REGEXP_LIKE due to regular expression too long

Question

I am getting an ORA-12733: regular expression too long error when trying to find if certain ids already inside the database.

regexp_like (','||a.IDs||',',',('||replace(b.IDs,',','|')||'),')

a.IDs and b.IDs are in a format of something like id=16069,16070,16071,16072,16099,16100.

i will replace comma with | in b so it will tell me if any of the number is matched. The length of both a.IDs and b.IDs might vary from different queries. Oracle regexp_like limit is only 512. Anyone know if other possible solutions?

Best solution: don't store comma-separated lists in text strings. — Bob Jarvis - Слава Україні, Dec 26 '17 at 09:02

score 0 · Answer 1 · answered Dec 26 '17 at 14:04

Why on earth do you store list of numbers as string?

Anyway, one possible solution is this one. Create TYPE and FUNCTION like this:

CREATE OR REPLACE TYPE NUMBER_TABLE_TYPE AS TABLE OF NUMBER;


CREATE OR REPLACE FUNCTION SplitArray(LIST IN VARCHAR2, Separator IN VARCHAR2) RETURN NUMBER_TABLE_TYPE IS
    OutTable NUMBER_TABLE_TYPE; 
BEGIN

    IF LIST IS NULL THEN
        RETURN NULL;
    ELSE
        SELECT REGEXP_SUBSTR(LIST, '[^'||Separator||']+', 1, LEVEL)
        BULK COLLECT INTO OutTable
        FROM dual
        CONNECT BY REGEXP_SUBSTR(LIST, '[^'||Separator||']+', 1, LEVEL) IS NOT NULL;
    END IF;

    IF OutTable.COUNT > 0 THEN
        RETURN OutTable;
    ELSE
        RETURN NULL;
    END IF;

END SplitArray;

Then you query for a single number as this:

WHERE 16071 MEMBER OF SplitArray(a.IDs, ',')

or for several numbers as this:

WHERE SplitArray(b.IDs, ',') SUBMULTISET OF SplitArray(a.IDs, ',')

Have a look at Multiset Conditions

Alternative to REGEXP_LIKE due to regular expression too long

1 Answers1