Dividing a string into equal parts for Oracle SQL IN clause

Data and Databases
1

I’ve got a problem with a long string in Oracle SQL. The string’s length is always a multiple of 5. I need to split it into chunks of 5 characters each. These chunks should then be used in an IN clause for a SELECT statement.

Here’s an example of what I mean:

-- Original string
my_string = '1234567890abcdefghij'

-- What I want to achieve
SELECT * FROM my_data 
WHERE code IN ('12345', '67890', 'abcde', 'fghij')

Is there a way to do this splitting in Oracle SQL? I’m not sure how to approach this. Any help would be great!

2

hey there! have u tried using LISTAGG? it might work:

WITH chunks AS (
SELECT LISTAGG(‘’‘’||SUBSTR(ur_string, LEVEL*5-4, 5)||‘’‘’, ‘,’)
WITHIN GROUP (ORDER BY LEVEL) AS chunk_list
FROM DUAL
CONNECT BY LEVEL <= LENGTH(ur_string)/5
)
SELECT * FROM ur_data WHERE code IN (SELECT chunk_list FROM chunks);

hope this helps!

3

You could use a combination of SUBSTR and REGEXP_SUBSTR for this. Here’s an approach:

WITH chunks AS (
SELECT REGEXP_SUBSTR(‘1234567890abcdefghij’, ‘.{1,5}’, 1, LEVEL) AS chunk
FROM DUAL
CONNECT BY REGEXP_SUBSTR(‘1234567890abcdefghij’, ‘.{1,5}’, 1, LEVEL) IS NOT NULL
)
SELECT * FROM my_data
WHERE code IN (SELECT chunk FROM chunks);

This method uses REGEXP_SUBSTR to extract 5-character chunks from your string. The CONNECT BY clause generates rows for each chunk until no more chunks are found. It’s efficient and works for varying string lengths, as long as they’re multiples of 5. Just replace the hardcoded string with your actual string variable in both places.

4

hey, have you tried a hierarchical query? like:

WITH chunks AS (
SELECT SUBSTR(your_str, (LEVEL-1)*5+1, 5) AS chunk
FROM DUAL
CONNECT BY LEVEL <= LENGTH(your_str)/5
)

SELECT * FROM my_data WHERE code IN (SELECT chunk FROM chunks);

will that work?