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  .

sticky bit · Accepted Answer · 2018-05-08 22:32:10Z

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('&#x2007;'),'g')),

For replacing zeros and commas:

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

See for an example on SQL Fiddel.

edited May 8, 2018 at 22:32

answered May 8, 2018 at 22:02

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

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

5 Comments

João Ciocca Over a year ago

Doesn't seem to work, I get ERROR: invalid regular expression: quantifier operand invalid, and googling around for the error, it seems pgSQL doesn't support positive lookbehinds?

sticky bit Over a year ago

It does, but only in 9.6 and above.

sticky bit Over a year ago

But maybe it is possible in <9.6 to first reverse the string, do the equivalent with lookaheads and reverse it back.

João Ciocca Over a year ago

I do think an upgrade is in order =) but thanks for the help, and considering I didn't mentioned it was prior to 9.6, the answer is accepted!

sticky bit Over a year ago

Thanks. And for the meanwhile I've edited my answer to include the workaround with reverse.

Collectives™ on Stack Overflow

How to regex_replace every instance found?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related