Postgres invalid regular expression: invalid character range

Question

I'm using the following line in a postgres function:

regexp_replace(input, '[^a-z0-9\-_]+', sep, 'gi');

But I'm getting ERROR: invalid regular expression: invalid character range when I try to use it. The regex works fine in Ruby, is there a reason it'd be different in postgres?

I don't know but if I were you I will try to put the hyphen at the end of the character class or I will try to escape it twice. — Casimir et Hippolyte
– Casimir et Hippolyte, Commented Jul 29, 2016 at 23:02
I run that with no error. Maybe provide the rest of your code? — Nick
– Nick, Commented Jul 29, 2016 at 23:03
This post may be useful: stackoverflow.com/questions/12069639/postgres-regex-issue — Casimir et Hippolyte
– Casimir et Hippolyte, Commented Jul 29, 2016 at 23:05
Postgres 9.5: select regexp_replace('a_b%d-e', '[^a-z0-9\-_]+', '|', 'gi') is ok and yields a_b|d-e. — klin
– klin, Commented Jul 29, 2016 at 23:46

MAP · Accepted Answer · 2016-07-29 23:08:11Z

11

Some regexp parsers will work with a dash (-) in the middle, if after a range like you have it, but others won't. I suspect the postgres regexp parser is in the later class. The canonical way to have the dash in a regexp is to start with it, i.e. change the regexp to '[^-a-z0-9_]+' which might get it past the parser. Some regexp parsers, however, can be really fussy and not accept that, either.

I don't have a postgres to test with, but I expect they'll accept the regexp above and deal correctly. Otherwise you have to find the regexp portion of their manual and understand what it says about this.

answered Jul 29, 2016 at 23:08

MAP

9027 silver badges11 bronze badges

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

2 Comments

user557597 Over a year ago

Is the dash - really all that different than any other non-alphanum literal ? Perhaps it doesn't accept escapes at all. In reality [^a-z0-9-_] contains a valid dash literal when outside the context of a range operator.

bodacious Dec 18, 2024 at 21:52

I was just debugging PostgreSQL 14 bug that was caused by this exact issue. Moving the dash to the front of the range fixed the issue. Astonishing. Thank you

Alexandre Andrade · Accepted Answer · 2021-05-06 20:06:04Z

2

I had the same problem

using

\-

instead of only

-

worked to me

answered May 6, 2021 at 20:06

Alexandre Andrade

1697 bronze badges

Comments

Daniel Krom · Accepted Answer · 2020-12-07 09:26:39Z

0

For me it worked to move the dash (-) to the end of the list

replaced [A-Za-z0-9-_.+=] with [A-Za-z0-9_.+=-] seems to work

answered Dec 7, 2020 at 9:26

Daniel Krom

10.1k3 gold badges47 silver badges45 bronze badges

Comments

Virendra Mishra · Accepted Answer · 2022-10-17 18:16:10Z

0

[^[:digit:]\-.]

The above code will work.

answered Oct 17, 2022 at 18:16

Virendra Mishra

11 silver badge2 bronze badges

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Postgres invalid regular expression: invalid character range

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related