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
– Wiktor Stribiżew, Commented Jun 18, 2020 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
– Wiktor Stribiżew, Commented Jun 18, 2020 at 20:08
Maybe that is due to spaces? Try \s*(?:[/+;]|and|with)\s*, or [/+;]|\s+(?:and|with)\s+ — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Jun 18, 2020 at 20:13

Wiktor Stribiżew · Accepted Answer · 2020-06-18 20:40:50Z

1

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.

answered Jun 18, 2020 at 20:40

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related