regexp_replace acts as if I've given it a global flag, but I have not

Question

Using regexp_replace within PostgreSQL, I've developed (with a lot of help from SO) a pattern to match the first n characters, if the last character is not in a list of characters I don't want the string to end in.

regexp_replace(pf.long_description, '(^.{1,150}[^ -:])', '\1...')::varchar(2000)

However, I would expect that to simply end the string in an ellipses. However what I get is the first 150 characters plus the ellipses at the end, but then the string continues all the way to the end.
Why is all that content not being eliminated?

Answer 1

Why is all that content not being eliminated?

because you haven't requested that. you've asked to have the first 2-151 characters replaced with those same characters and elipsis. if you modify the pattern to be (^.{1,150}[^ -:]).* (notice the trailing .* has regex_replace work on the complete string, not just the prefix) you should get the desired effect.

Answer 2

Do your really want the range of characters between the space character and the colon: [^ -:]?

To include a literal - in a character class, put it first or last. Looks like you might actually want [^ :-] - that's just excluding the three characters listed.
Details about bracket expressions in the manual.

That whould be (building on what @just already provided):

SELECT regexp_replace(pf.long_decript-ion, '(^.{1,150}[^ :-]).*$', '\1...');

But it should be cheaper to use substring() instead:

SELECT substring(pf.long_decript-ion, '^.{1,150}[^ :-]') || '...';