How can you replace the last occurrence of a character in a string (text column)?

Let's say I have a text field with this value in Postgres:

'bar$foo$john$doe$xxx'

I'd like to replace just the last occurrence of the dollar ($) character with another character, for example '-'. After the replacement, the contents of the field should be:

'bar$foo$john$doe-xxx'

Introduction:

This problem involves a bit of lateral thinking. The last occurrence of any character is also the first occurrence of that character in the string when it is reversed! All of the solutions (bar one) use this approach.

For all of the 5 solutions presented, we have the following (a fiddle with all the code below is available here. An individual fiddle with each solution separately is included with each solution below):

CREATE TABLE test
(
  id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  t_field TEXT
);

The PRIMARY KEY is only required by the 5th solution. Then we run the following queries to populate the table - the first record is the OP's own data - the rest is randomly generated!

OP:

INSERT INTO test (t_field)
VALUES ('bar$foo$john$doe$xxx');  -- OP's own data

Random data:

INSERT INTO test (t_field)
SELECT 
  LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

SELECT
  t_field, 
  OVERLAY(t_field PLACING '-' 
    FROM 
      LENGTH(t_field) + 1 - STRPOS(REVERSE(t_field), '

Peformance: time for 10M records = 8034.787 ms.



Comparison with fastest = 1.0 x



2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

SELECT 
  REVERSE(REGEXP_REPLACE(REVERSE(t_field), '\

What is being done (from the inside out) is:

- 
REVERSE() the string,

- 
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

- 
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

Peformance: time for 10M records = 14298.643 ms.



Comparison with fastest = 1.77 x



3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

SELECT
  t_field,
  REGEXP_REPLACE(t_field, '(.*)\

- 
Peformance: time for 10M records = 16316.768 ms.

- 
Comparison with fastest =  2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

SELECT
  t_field,
  REVERSE(
  SUBSTRING(REVERSE(t_field) FROM 1 FOR POSITION('

Peformance: time for 10M records = 16316.768 ms.



Comparison with fastest =  2.34 x



5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1  (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n ||
  LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

Peformance: time for 10M records = 8034.787 ms.



Comparison with fastest = 1.0 x



2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

- 
REVERSE() the string,

- 
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

- 
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

Peformance: time for 10M records = 14298.643 ms.



Comparison with fastest = 1.77 x



3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

- 
Peformance: time for 10M records = 16316.768 ms.

- 
Comparison with fastest =  2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

Peformance: time for 10M records = 16316.768 ms.



Comparison with fastest =  2.34 x



5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1  (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n ||
  LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

Peformance: time for 10M records = 8034.787 ms.



Comparison with fastest = 1.0 x



2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

- 
REVERSE() the string,

- 
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

- 
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

Peformance: time for 10M records = 14298.643 ms.



Comparison with fastest = 1.77 x



3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

- 
Peformance: time for 10M records = 16316.768 ms.

- 
Comparison with fastest =  2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

Peformance: time for 10M records = 16316.768 ms.



Comparison with fastest =  2.34 x



5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1  (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n ||
  LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

Peformance: time for 10M records = 8034.787 ms.



Comparison with fastest = 1.0 x



2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

- 
REVERSE() the string,

- 
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

- 
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

Peformance: time for 10M records = 14298.643 ms.



Comparison with fastest = 1.77 x



3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

- 
Peformance: time for 10M records = 16316.768 ms.

- 
Comparison with fastest =  2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

Peformance: time for 10M records = 16316.768 ms.



Comparison with fastest =  2.34 x



5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1  (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n ||
  LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT)
FROM 
  GENERATE_SERIES(1, 29999);  --fiddle server too hard!
--
-- The home test VM used 10,000,000 records - 16 GB RAM, 1 CPU, SSD
--


The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

Peformance: time for 10M records = 8034.787 ms.



Comparison with fastest = 1.0 x



2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

- 
REVERSE() the string,

- 
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

- 
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

Peformance: time for 10M records = 14298.643 ms.



Comparison with fastest = 1.77 x



3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

- 
Peformance: time for 10M records = 16316.768 ms.

- 
Comparison with fastest =  2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

Peformance: time for 10M records = 16316.768 ms.



Comparison with fastest =  2.34 x



5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1  (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n)
         ) AS result
FROM test;


Peformance: time for 10M records = 8034.787 ms.



Comparison with fastest = 1.0 x



2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

- 
REVERSE() the string,

- 
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

- 
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

Peformance: time for 10M records = 14298.643 ms.



Comparison with fastest = 1.77 x



3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

- 
Peformance: time for 10M records = 16316.768 ms.

- 
Comparison with fastest =  2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

Peformance: time for 10M records = 16316.768 ms.



Comparison with fastest =  2.34 x



5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1  (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n ||
  LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

Peformance: time for 10M records = 8034.787 ms.



Comparison with fastest = 1.0 x



2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

- 
REVERSE() the string,

- 
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

- 
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

Peformance: time for 10M records = 14298.643 ms.



Comparison with fastest = 1.77 x



3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

- 
Peformance: time for 10M records = 16316.768 ms.

- 
Comparison with fastest =  2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

Peformance: time for 10M records = 16316.768 ms.



Comparison with fastest =  2.34 x



5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1  (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n ||
  LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

Peformance: time for 10M records = 8034.787 ms.



Comparison with fastest = 1.0 x



2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

- 
REVERSE() the string,

- 
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

- 
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

Peformance: time for 10M records = 14298.643 ms.



Comparison with fastest = 1.77 x



3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

- 
Peformance: time for 10M records = 16316.768 ms.

- 
Comparison with fastest =  2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

Peformance: time for 10M records = 16316.768 ms.



Comparison with fastest =  2.34 x



5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1  (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n ||
  LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

Peformance: time for 10M records = 8034.787 ms.



Comparison with fastest = 1.0 x



2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

- 
REVERSE() the string,

- 
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

- 
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

Peformance: time for 10M records = 14298.643 ms.



Comparison with fastest = 1.77 x



3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

- 
Peformance: time for 10M records = 16316.768 ms.

- 
Comparison with fastest =  2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

Peformance: time for 10M records = 16316.768 ms.



Comparison with fastest =  2.34 x



5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1  (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n ||
  LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT)
FROM 
  GENERATE_SERIES(1, 29999);  --fiddle server too hard!
--
-- The home test VM used 10,000,000 records - 16 GB RAM, 1 CPU, SSD
--


The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

Peformance: time for 10M records = 8034.787 ms.



Comparison with fastest = 1.0 x



2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

- 
REVERSE() the string,

- 
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

- 
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

Peformance: time for 10M records = 14298.643 ms.



Comparison with fastest = 1.77 x



3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

- 
Peformance: time for 10M records = 16316.768 ms.

- 
Comparison with fastest =  2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

Peformance: time for 10M records = 16316.768 ms.



Comparison with fastest =  2.34 x



5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1  (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n, '-'))
FROM
  test;


What is being done (from the inside out) is:

- 
REVERSE() the string,

- 
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

- 
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

Peformance: time for 10M records = 14298.643 ms.



Comparison with fastest = 1.77 x



3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

- 
Peformance: time for 10M records = 16316.768 ms.

- 
Comparison with fastest =  2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

Peformance: time for 10M records = 16316.768 ms.



Comparison with fastest =  2.34 x



5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1  (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n ||
  LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

Peformance: time for 10M records = 8034.787 ms.



Comparison with fastest = 1.0 x



2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

- 
REVERSE() the string,

- 
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

- 
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

Peformance: time for 10M records = 14298.643 ms.



Comparison with fastest = 1.77 x



3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

- 
Peformance: time for 10M records = 16316.768 ms.

- 
Comparison with fastest =  2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

Peformance: time for 10M records = 16316.768 ms.



Comparison with fastest =  2.34 x



5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1  (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n ||
  LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

Peformance: time for 10M records = 8034.787 ms.



Comparison with fastest = 1.0 x



2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

- 
REVERSE() the string,

- 
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

- 
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

Peformance: time for 10M records = 14298.643 ms.



Comparison with fastest = 1.77 x



3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

- 
Peformance: time for 10M records = 16316.768 ms.

- 
Comparison with fastest =  2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

Peformance: time for 10M records = 16316.768 ms.



Comparison with fastest =  2.34 x



5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1  (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n ||
  LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

Peformance: time for 10M records = 8034.787 ms.



Comparison with fastest = 1.0 x



2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

- 
REVERSE() the string,

- 
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

- 
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

Peformance: time for 10M records = 14298.643 ms.



Comparison with fastest = 1.77 x



3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

- 
Peformance: time for 10M records = 16316.768 ms.

- 
Comparison with fastest =  2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

Peformance: time for 10M records = 16316.768 ms.



Comparison with fastest =  2.34 x



5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1  (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n ||
  LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT)
FROM 
  GENERATE_SERIES(1, 29999);  --fiddle server too hard!
--
-- The home test VM used 10,000,000 records - 16 GB RAM, 1 CPU, SSD
--

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n) ) AS result FROM test;

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) FROM GENERATE_SERIES(1, 29999); --fiddle server too hard! -- -- The home test VM used 10,000,000 records - 16 GB RAM, 1 CPU, SSD --

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n, '\1-' ) FROM test LIMIT 2;

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) FROM GENERATE_SERIES(1, 29999); --fiddle server too hard! -- -- The home test VM used 10,000,000 records - 16 GB RAM, 1 CPU, SSD --

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n) ) AS result FROM test;

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) FROM GENERATE_SERIES(1, 29999); --fiddle server too hard! -- -- The home test VM used 10,000,000 records - 16 GB RAM, 1 CPU, SSD --

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n, '-')) FROM test;

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) FROM GENERATE_SERIES(1, 29999); --fiddle server too hard! -- -- The home test VM used 10,000,000 records - 16 GB RAM, 1 CPU, SSD --

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n) ) AS result FROM test;

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) FROM GENERATE_SERIES(1, 29999); --fiddle server too hard! -- -- The home test VM used 10,000,000 records - 16 GB RAM, 1 CPU, SSD --

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n IN REVERSE(t_field)) - 1) || '-' || SUBSTRING(REVERSE(t_field) FROM POSITION('

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) FROM GENERATE_SERIES(1, 29999); --fiddle server too hard! -- -- The home test VM used 10,000,000 records - 16 GB RAM, 1 CPU, SSD --

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n) ) AS result FROM test;

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) FROM GENERATE_SERIES(1, 29999); --fiddle server too hard! -- -- The home test VM used 10,000,000 records - 16 GB RAM, 1 CPU, SSD --

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n, '-')) FROM test;

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) FROM GENERATE_SERIES(1, 29999); --fiddle server too hard! -- -- The home test VM used 10,000,000 records - 16 GB RAM, 1 CPU, SSD --

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n) ) AS result FROM test;

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) FROM GENERATE_SERIES(1, 29999); --fiddle server too hard! -- -- The home test VM used 10,000,000 records - 16 GB RAM, 1 CPU, SSD --

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n, '\1-' ) FROM test LIMIT 2;

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) FROM GENERATE_SERIES(1, 29999); --fiddle server too hard! -- -- The home test VM used 10,000,000 records - 16 GB RAM, 1 CPU, SSD --

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n) ) AS result FROM test;

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) FROM GENERATE_SERIES(1, 29999); --fiddle server too hard! -- -- The home test VM used 10,000,000 records - 16 GB RAM, 1 CPU, SSD --

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n, '-')) FROM test;

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) FROM GENERATE_SERIES(1, 29999); --fiddle server too hard! -- -- The home test VM used 10,000,000 records - 16 GB RAM, 1 CPU, SSD --

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n) ) AS result FROM test;

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) FROM GENERATE_SERIES(1, 29999); --fiddle server too hard! -- -- The home test VM used 10,000,000 records - 16 GB RAM, 1 CPU, SSD --

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n IN REVERSE(t_field)) + 1 FOR (LENGTH(REVERSE(t_field))))) FROM test LIMIT 2;

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) FROM GENERATE_SERIES(1, 29999); --fiddle server too hard! -- -- The home test VM used 10,000,000 records - 16 GB RAM, 1 CPU, SSD --

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n) ) AS result FROM test;

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) FROM GENERATE_SERIES(1, 29999); --fiddle server too hard! -- -- The home test VM used 10,000,000 records - 16 GB RAM, 1 CPU, SSD --

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n, '-')) FROM test;

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) FROM GENERATE_SERIES(1, 29999); --fiddle server too hard! -- -- The home test VM used 10,000,000 records - 16 GB RAM, 1 CPU, SSD --

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n) ) AS result FROM test;

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) FROM GENERATE_SERIES(1, 29999); --fiddle server too hard! -- -- The home test VM used 10,000,000 records - 16 GB RAM, 1 CPU, SSD --

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n, '\1-' ) FROM test LIMIT 2;

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) FROM GENERATE_SERIES(1, 29999); --fiddle server too hard! -- -- The home test VM used 10,000,000 records - 16 GB RAM, 1 CPU, SSD --

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n) ) AS result FROM test;

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) FROM GENERATE_SERIES(1, 29999); --fiddle server too hard! -- -- The home test VM used 10,000,000 records - 16 GB RAM, 1 CPU, SSD --

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n, '-')) FROM test;

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) FROM GENERATE_SERIES(1, 29999); --fiddle server too hard! -- -- The home test VM used 10,000,000 records - 16 GB RAM, 1 CPU, SSD --

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n) ) AS result FROM test;

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) FROM GENERATE_SERIES(1, 29999); --fiddle server too hard! -- -- The home test VM used 10,000,000 records - 16 GB RAM, 1 CPU, SSD --

The solutions will be presented in order of performance. It was tested on both db<>fiddle and on a home VM (16GB RAM, SSD) with 10M records in the test table - don't try 10M on the fiddle! Each method is given a factor in terms of how much longer it took than the fastest on the VM.

In all cases, the desired result of bar$foo$john$doe-xxx is obtained for the OP's original data and the test queries (with LIMIT 2 show that they are behaving as expected - i.e. replacing the last dollar ($) sign with a hyphen (-). You may vary this limit on the fiddle to check.
1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):

%%CODEBLOCK_3%%

• Peformance: time for 10M records = 8034.787 ms.

• Comparison with fastest = 1.0 x

2: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle):

%%CODEBLOCK_4%%

What is being done (from the inside out) is:

-
REVERSE() the string,

-
run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string.

Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read ... , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.

Note also that $ is a regex meta-character - i.e. it has special functionality within regular expressions (it means the last character of a string) and therefore it has to be escaped with the backslash (\) character when replacing it.

-
then, the final step is to reverse our edited string back to its original order and we have the result!

It is worth bearing in mind that regular expressions are incredibly powerful. Unfortunately (paraphrasing), with great power comes great complexity. Regexes can become convoluted and difficult to understand very easily - but they are well worth dipping into - they can turn pages of code into one-liners in the hands of an adept!

It is always worthwhile trying to find a different solution with the non-regex functionality first (c.f. solution 1), but they have their place and in this case, it works reasonably well! The site linked to above is a good place to start exploring them.

• Peformance: time for 10M records = 14298.643 ms.

• Comparison with fastest = 1.77 x

3: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)):

%%CODEBLOCK_5%%

-
Peformance: time for 10M records = 16316.768 ms.

-
Comparison with fastest = 2.03 x

4: Alternative string function only, uses SUBSTRING(), POSITION() and LENGTH() (individual fiddle):

%%CODEBLOCK_6%%

• Peformance: time for 10M records = 16316.768 ms.

• Comparison with fastest = 2.34 x

5: ARRAY (manual) - v. slow but demonstrates STRING_TO_ARRAY(), UNNEST() and WITH ORDINALITY1 (individual fiddle)

1: See these posts (1, 2 & 3) by Erwin Brandstetter on WITH ORDINALITY

The individual fiddle shows a number of approaches together with performance analysis and some discusson. Included for completeness only and n