select version();

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

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

INSERT INTO test (t_field) SELECT LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '$' || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '$' || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '$' || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '$' || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) FROM GENERATE_SERIES(1, 299999); --<<== Vary here -- -- Vary the GENERATE_SERIES(1, 299999) number if the fiddle run fails! -- -- 299999 (+ 1 - OP) = 300,000 = large enough number to get reliable, consistent results -- and small enough so that the fiddle run doesn't fail too often. --

ANALYZE test;

-- -- 4: Another "pure" string function approach - very much less efficient than -- the fastest -- SELECT t_field, REVERSE( SUBSTRING(REVERSE(t_field) FROM 1 FOR POSITION('$' IN REVERSE(t_field)) - 1) || '-' || SUBSTRING(REVERSE(t_field) FROM POSITION('$' IN REVERSE(t_field)) + 1 FOR (LENGTH(REVERSE(t_field))))) FROM test LIMIT 2;

EXPLAIN(ANALYZE, TIMING OFF) SELECT REVERSE( SUBSTRING (REVERSE(t_field) FROM 1 FOR (LENGTH((STRING_TO_ARRAY(REVERSE(t_field), '$'))[1]))) || '-' || SUBSTRING (REVERSE(t_field) FROM 5 FOR (LENGTH(REVERSE(t_field))))) AS result FROM test;