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?

Answer 1

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.