CODEWITHSUNDEEP
regexpostgresql
Evgeny Nozdrev
Evgeny Nozdrev

Reputation: 1566

Regular expression exclusion in PostgreSQL

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.

Upvotes: 1

Views: 791

Answers (2)

Emilio Platzer
Emilio Platzer

Reputation: 2469

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'(?<!\\\\),'));

Upvotes: 4

tomersss2
tomersss2

Reputation: 155

You can also split it to groups first:

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

( and later on concatenate them with comma)

Upvotes: 0

Related Questions