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

How can we convert a string of any length into a comma separated string with comma after every n characters. I am using Oracle 10g and above. I tried with REGEXP_SUBSTR but couldn't get desired result.

e.g.: for below string comma after every 5 characters.

input: 

aaaaabbbbbcccccdddddeeeeefffff

output: 

aaaaa,bbbbb,ccccc,ddddd,eeeee,fffff,

or 

aaaaa,bbbbb,ccccc,ddddd,eeeee,fffff

Thanks in advance.

sqloracle


Solution 1:[1]

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.

Solution 2:[2]

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

Solution 3:[3]

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 ;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Boneist
Solution 2 Kaushik Nayak
Solution 3 Barbaros Özhan

Related Questions