ORACLE PL-SQL how to create function to split a string and return n-long "chunks" into an array?

Question

I need to create a function that will accept a sting input of any length and return an array of strings each containing n long chunks. For example, an input of This is a test with 3 character long chunks should return:

Thi
s i
s a
tes
t

I have created the following function to do so. My question is, is there possibly a better, faster way to approach this? I know that this function may be called many times using very long strings and I do not wan't this to possibly slow down the system. Additionally, I eventuallly need to set the function up so that it also creates a new entry upon detection of a delimiter. For example, with a "chunk length" of three:

Testing with comma delimiters, one, two, three, test

Should return:

Tes
tin
g w
ith
co
mma
del
imi
ter
s,
one
,
two
,
thr
ee,
te
st

Notice that i do not want the delimiter it's self to be deleted or replaced. I just have a new line/new array entry populate just after detection.

Here is my code so far:

CREATE OR REPLACE FUNCTION SPLIT_STRING (
    p_str   VARCHAR2, --String to split
    p_del   VARCHAR2, --Delimiter
    p_len   INTEGER,  --Length of each chunk
    p_force NUMBER) --Forces split when length is reached (1=on, 0=off)
RETURN VARCHAR2 IS
    l_tmp_str   VARCHAR2(32767);
    l_chnk_len  INTEGER;
    l_str       VARCHAR2(32767);
    l_chunk     VARCHAR2(32767);
    l_pos       INTEGER;
    l_len       INTEGER;
    l_chnksize  NUMBER;
BEGIN
    --Determine the strings total length
    l_len:= LENGTH(p_str);
    IF (l_len > 0)
    THEN
        l_tmp_str:= p_str;

        --Determine the necessary number of chuncks
        l_chnksize:=(l_len/p_len);
        IF MOD(l_chnksize,1) != 0
        THEN
            l_chnksize:= CEIL(l_chnksize);
        END IF;

        --Split the string into chunks
        IF p_force = 1
        THEN
            l_pos:=1;    
            FOR loop_num IN 1..l_chnksize
            LOOP
                IF (loop_num>1)
                THEN
                    l_str:=l_str||CHR(10)||CHR(13)||SUBSTR(p_str,l_pos,p_len);
                ELSE
                    l_str:=SUBSTR(p_str,l_pos,p_len);
                END IF;

                --Increment position placeholder
                l_pos:=l_pos+p_len;
            END LOOP;
        ELSE
            l_str:='UNFORCED, NOT IMPLEMENTED'; 
        END IF;
    END IF;

--Return the delimited string
RETURN l_str;

My specific question is: Is there a FASTER way to do this for LARGE string inputs?

You don't need to bother about large string. Your max input size is 32767. And i think execution this function will take < 0.01 s for the largest input. — Arkadiusz Łukasiewicz
– Arkadiusz Łukasiewicz, Commented May 6, 2016 at 6:38

Noel · Accepted Answer · 2016-05-06 08:54:43Z

2

I don't know if this is faster, but definitely simpler. You are not actually putting the chunks in arrays, but inserting newline character after every delimiter or a group of characters. This can be easily done using regular expressions.

select regexp_replace('Testing with comhm,a sdfdeli,mitjers,one,two,three,test',
                      '(.{0,3},)|(.{5})',
                      '\1\2' ||chr(10))     chunks
from dual;

CHUNKS
-------
Testi
ng wi
th co
mhm,
a sdf
deli,
mitje
rs,
one,
two,
three
,
test

Regex Explanation:

(.{0,3},) : Group of up to 3 characters followed by a comma(delimiter), Assuming 5 as the length of each chunk.
(.{5}) : Group of 5 characters, Assuming 5 as the length of each chunk.

These first and second capture groups are replaced by themselves appended with a newline character.

Generic expression would be,

'(.{0,'||(length-2)||'}'||delimiter||')|(.{'||(length)||'})'

edited May 6, 2016 at 8:54

answered May 6, 2016 at 8:38

Noel

10.6k30 gold badges53 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hooplator15 Over a year ago

Thanks for the reply, this does indeed work. Interestingly, after modifying my code to do EXACTLY the same thing as your regex using a LOOP structure rather than your regex and testing 10,000 times in a LOOP for a 3000 Char length string, the LOOP method performs better. I did not expect that. None the less, thank you.

Collectives™ on Stack Overflow

ORACLE PL-SQL how to create function to split a string and return n-long "chunks" into an array?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related