How to regex_replace every instance found?

Question

Building up on this answer I thought of using PostgreSQL's regexp_replace function along with to_char to make a PHP created, SQL-fed table have a column of numbers be centralized, but right aligned.

On the HTML front, the column is already centralized, but that makes numbers with a different length to not align with each other to the right. The idea on that answer would be to make all numbers have the same length, leading with empty spaces, to make them be aligned to the right.

I got as far as trying regexp_replace(to_char(field,'FM000,000,000'),$$^(0{1,3},?)+$$,' '), but this will make EVERY 0 to the left, along with grouping ,, to be replaced by a single HTML  .

Can someone suggest a way to change every 0 into  ? I'm pretty sure the same solution can then be applied to another regexp_replace so I can replace any "left grouping ," into  .

Answer 1

Try this:

regexp_replace(to_char(field,'FM000,000,000'),$$(?<=^[0,]*)0$$,'&#x2007;','g')

It uses a positive lookbehind ((?<=^[0,]*)), so that only zeros match, where a sequence of zeros or commas ([0,]) up to the beginning (^) is before them. Together with the global modifier ('g', the extra argument to the regexp_replace() function compared to yours) all such zeros are replaced, as with the global modifier not only the first match is replaced but any.

Oh wait, now I'm not sure if you wanted only the zeros replaced or the commas too? If so use this:

regexp_replace(to_char(field,'FM000,000,000'),$$(?<=^[0,]*)[0,]$$,'&#x2007;','g')

Instead of a zero it matches a zero or a comma [0,]. The rest is like above.

But lookaheads are only supported since version 9.6. As a workaround one could reverse the string first, do the equivalent with lookbehinds (which are supported in lower versions) and reverse it back.

For replacing only zeros:

reverse(regexp_replace(reverse(to_char(field,'FM000,000,000')),'0(?=[0,]*$)',reverse(' '),'g')),

For replacing zeros and commas:

reverse(regexp_replace(reverse(to_char(field,'FM000,000,000')),'[0,](?=[0,]*$)',reverse(' '),'g'))

See for an example on SQL Fiddel.