Oracle SQL query to convert a string into a comma separated string with comma after every n characters

This can be done with regexp_replace, like so:

WITH sample_data AS (SELECT 'aaaaabbbbbcccccdddddeeeeefffff' str FROM dual UNION ALL
            SELECT 'aaaa' str FROM dual UNION ALL
            SELECT 'aaaaabb' str FROM dual)
SELECT str,
       regexp_replace(str, '(.{5})', '\1,')
FROM   sample_data;

STR                            REGEXP_REPLACE(STR,'(.{5})','\
------------------------------ --------------------------------------------------------------------------------
aaaaabbbbbcccccdddddeeeeefffff aaaaa,bbbbb,ccccc,ddddd,eeeee,fffff,
aaaa                           aaaa
aaaaabb                        aaaaa,bb

The regexp_replace simply looks for any 5 characters (.{5}), and then replaces them with the same 5 characters plus a comma. The brackets around the .{5} turn it into a labelled subexpression - \1, since it's the first set of brackets - which we can then use to represent our 5 characters in the replacement section.

You would then need to trim the extra comma off the resultant string, if necessary.

SELECT RTRIM ( REGEXP_REPLACE('aaaaabbbbbcccccdddddeeeeefffff', '(.{5})' ,'\1,') ,',') replaced
FROM DUAL;

This worked for me:

WITH strlen AS
    (
        SELECT 'aaaaabbbbbcccccdddddeeeeefffffggggg'      AS input,
            LENGTH('aaaaabbbbbcccccdddddeeeeefffffggggg') AS LEN,
            5                                             AS part
        FROM dual
    )
    ,
    pattern AS
    (
        SELECT regexp_substr(strlen.input, '[[:alnum:]]{5}', 1, LEVEL)
            ||',' AS line
        FROM strlen,
            dual
            CONNECT BY LEVEL <= strlen.len / strlen.part
    )
SELECT rtrim(listagg(line, '') WITHIN GROUP (
ORDER BY 1), ',') AS big_bang$
FROM pattern ;