Search code examples
oracleteradata

REGEXP_REPLACE Insert a character after each alpha-numeric group

Let's say I have a string 'ABCSOSLOL', and I'm trying to get it to become 'ABC,SOS,LOL,'

I have been able to accomplish this via this call

SELECT REGEXP_REPLACE('ABCSOSLOL',([[:alpha:]])([[:alpha:]])([[:alpha:]]),'\1\2\3,',1,0);

However, this doesn't work if the string becomes 'AB1SOSLO9' - since now it has to be alphanumeric.

Changing :alpha: to [a-z0-9] does not inject a comma. Is there an alphanumeric substitute for :alpha: or a better method?

Trying this in Teradata, and Oracle.

Solution

  • In Oracle you can use:

    SELECT REGEXP_REPLACE('ABC123XY4', '([[:alnum:]]{3})', '\1,') FROM DUAL

    or:

    SELECT REGEXP_REPLACE('ABC123XY4', '([a-zA-Z0-9]{3})', '\1,') FROM DUAL

    Which output:

    REGEXP_REPLACE('ABC123XY4','([[:ALNUM:]]{3})','\1,')
    ABC,123,XY4,

    If you want to get rid of the trailing comma then you can use RTRIM(REGEXP_REPLACE('ABC123XY4', '([[:alnum:]]{3})', '\1,'), ',').

    fiddle