Format string using given pattern and delimeter in PostgreSQL

Question

I am trying to write a PostgreSQL query for below scenario:

Input:
I) alphanumeric string, e.g. abcd1234efgh
II) pattern, e.g. [1][2][3][2][1][3]
III) delimiter, e.g - (hyphen)

Output:
a-bc-d12-34-e-fgh

I require a query. Function won't work for me.

Answer 1

Use one regexp_replace() call:

SELECT regexp_replace('abcd1234efgh'
                     ,'^(.)(..)(...)(..)(.)(...)'
                     ,'\1-\2-\3-\4-\5-\6'
                     )

Produces the requested result.

The same written with numbers:

SELECT regexp_replace('abcd1234efgh'
                     ,'^(.{1})(.{2})(.{3})(.{2})(.{1})(.{3})'
                     ,'\1-\2-\3-\4-\5-\6'
                     )

Answer 2

select concat_ws('-',
    left(a, 1),
    substring(a from 2 for 2),
    substring(a from 4 for 3),
    substring(a from 7 for 2),
    substring(a from 9 for 1),
    right(a, 3)
)
from (values ('abcd1234efgh')) s(a)

The concat_ws function will use its first argument as a separator.