Regular expression exclusion in PostgreSQL

Question

I have to split some string in PostgreSQL on ',' but not on '\,' (backslash is escape character). For example, regexp_split_to_array('123,45,67\,89', ???) must split the string to array {123, 45, "67\,89"}.

What done already: E'(?<!3),' works with '3' as escape character. But how can I use the backslash instead of 3?

Does not work:

E'(?<!\),' does not split the string at all

E'(?<!\\),' throws error "parentheses () not balanced"

E'(?<!\ ),' (with space) splits on all ',' including '\,'

E'(?<!\\ ),' (with space) splits on all ',' too.

Is a literal backslash escaped in the input? What is the expected output for `'123,45,67\\,89'`? — Wiktor Stribiżew, Dec 25 '17 at 15:16
Please *always* declare your version of Postgres. And in this particular case also the setting for `standard_conforming_strings`. See: https://stackoverflow.com/a/12320729/939860 — Erwin Brandstetter, Dec 26 '17 at 06:11

score 4 · Accepted Answer · edited Dec 26 '17 at 06:14

The letter E in front of the text means C string and then you must escape twice, one for the C string and one for the regexp.

Try with and without E:

regexp_split_to_array('123,45,67\,89', '(?<!\\),')
regexp_split_to_array('123,45,67\,89', E'(?<!\\\\),')

Here http://rextester.com/VEE84838 a running example (unnest() is just for row by row display of results):

select unnest(regexp_split_to_array('123,45,67\,89', '(?<!\\),'));
select unnest(regexp_split_to_array('123,45,67\,89', E'(?<!\\\\),'));

score 0 · Answer 2 · answered Dec 25 '17 at 15:34

0

You can also split it to groups first:

(\d+),(\d+\,\d+)?

( and later on concatenate them with comma)

answered Dec 25 '17 at 15:34

tomersss2

135
12

Regular expression exclusion in PostgreSQL

2 Answers2