Split a column into multiple rows based on multiple delimiters in Postgres

Question

I have following table in postgres 11.

col1
a;b;c
d/e/f
g and h
i with k
l + m

I would like to split this table based on certain delimiters (/,+,;,and,with]). The desired output is:

col1          col2
a;b;c         a
a;b;c         b
a;b;c         c
d/e/f         d
d/e/f         e
d/e/f         f
g and h       g
g and h       h
i with k      i
i with k      k
l + m         l
l + m         m

I am trying out below query.


select distinct * from table t,
UNNEST(REGEXP_SPLIT_TO_ARRAY(t.col1, '\s+[/|+|;|and|with]\s+')) s(col2)

Replace `[/|+|;|and|with]` with `(?:[/+;]|and|with)`, a grouping construct. `[...]` is a bracket expression that only matches a single char defined inside it. — Wiktor Stribiżew, Jun 18 '20 at 20:03
Non-capturing groups are a natural choice when the value matched by a grouping is not going to be retrieved/used later. — Wiktor Stribiżew, Jun 18 '20 at 20:08
Maybe that is due to spaces? Try `\s*(?:[/+;]|and|with)\s*`, or `[/+;]|\s+(?:and|with)\s+` — Wiktor Stribiżew, Jun 18 '20 at 20:13

score 1 · Accepted Answer · answered Jun 18 '20 at 20:40

You may use

[/+;]|\s+(?:and|with)\s+
\s*(?:[/+;]|and|with)\s*

See the regex demo #1 and regex demo #2.

Details

[/+;] - a +, / or ; char -| - or
\s+(?:and|with)\s+ - an and or with words enclosed with one or more whitespaces
\s*(?:[/+;]|and|with)\s* matches a /, +, ; or and or with strings enclosed with 0 or more whitespace chars.

Note (?:...) is a non-capturing group that is a natural choice when the value matched by a grouping construct is not going to be retrieved/used later.

Split a column into multiple rows based on multiple delimiters in Postgres

1 Answers1