clear markdown compare help best fiddles feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601408 fiddles created (47997 in the last week).

select version();
version
PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit
 hidden batch(es)


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


INSERT INTO test (t_field) VALUES ('bar$foo$john$doe$xxx'); -- OP's own data
1 rows affected
 hidden batch(es)


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. --
299999 rows affected
 hidden batch(es)


ANALYZE test;
 hidden batch(es)


-- --
 hidden batch(es)


-- -- Approach 1: The fastest - using OVERLAY(), STRPOS() & LENGTH() -- SELECT t_field, OVERLAY(t_field PLACING '-' FROM LENGTH(t_field) + 1 - STRPOS(REVERSE(t_field), '$') ) AS result FROM test LIMIT 2;
t_field result
bar$foo$john$doe$xxx bar$foo$john$doe-xxx
14df$f735f$43233$6de$6b279 14df$f735f$43233$6de-6b279
 hidden batch(es)


EXPLAIN (ANALYZE, TIMING OFF) SELECT OVERLAY(t_field PLACING '-' FROM LENGTH(t_field) + 1 - STRPOS(REVERSE(t_field), '$') ) AS result FROM test;
QUERY PLAN
Seq Scan on test (cost=0.00..9718.00 rows=300000 width=32) (actual rows=300000 loops=1)
Planning Time: 0.029 ms
Execution Time: 253.435 ms
 hidden batch(es)