Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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'
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) || '$' ||
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, 29999); --<<== Vary here
--
-- For this fiddle, we only have 30,000 (29,999 + the OP's original datum) records
-- (although relative magnitudes appear good), the individual fiddles use
-- 300,000.
--
-- 300,000 appears large enough to give reliable consistent results and small
-- enough so that the fiddle doesn't fail too often - rarely fails on 30k.
--
--
-- You can vary this number, but please consider using the individual fiddles for
-- large numbers of records so as not to hit the db<>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.
OVERLAY(), STRPOS() AND REVERSE() (individual fiddle):SELECT
t_field,
OVERLAY(t_field PLACING '-'
FROM
LENGTH(t_field) + 1 - STRPOS(REVERSE(t_field), '$')
) AS result
FROM test;
SELECT
REVERSE(REGEXP_REPLACE(REVERSE(t_field), '\$', '-'))
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.
SELECT
t_field,
REGEXP_REPLACE(t_field, '(.*)\$', '\1-' )
FROM test
LIMIT 2;
Peformance: time for 10M records = 16316.768 ms.
Comparison with fastest = 2.03 x
SUBSTRING(), POSITION() and LENGTH() (individual fiddle):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;
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 not, in this case, as a realistic choice.
Despite, in this particular case, the ARRAY technique is not very performant (by virtue of having subqueries), much of the backend code of the server uses ARRAYs and they can frequently be the optimum method to tackle various problems. It is well worth getting to know this little-known corner of PostgreSQL.
The first thing is to do this:
SELECT
UNNEST
(STRING_TO_ARRAY(REVERSE((SELECT t.t_field
FROM test t
WHERE t.id = 1
)), '$'));
Result (The OP's record - note xxx comes first because of the REVERSE()):
str
xxx
eod
nhoj
oof
rab
The string is split into fields by the $ character.
Then:
SELECT
t.t_field,
t.id, x.elem, x.num
FROM test t
LEFT JOIN LATERAL
UNNEST(STRING_TO_ARRAY(REVERSE((SELECT t_field
FROM test
WHERE test.id = t.id
)), '$'))
WITH ORDINALITY AS x (elem, num) ON TRUE
LIMIT 5;
Result:
t_field id elem num
bar$foo$john$doe$xxx 1 xxx 1
bar$foo$john$doe$xxx 1 eod 2
bar$foo$john$doe$xxx 1 nhoj 3
bar$foo$john$doe$xxx 1 oof 4
bar$foo$john$doe$xxx 1 rab 5
The reason that we need the WITH ORDINALITY is that without is, we can't distinguish between the 1st element of the string (i.e. the one that interests us) and the others (elem, num);
Then, we do this:
SELECT
(SELECT t_field FROM test WHERE test.id = tab.id),
REVERSE(
(STRING_TO_ARRAY((SELECT REVERSE(t_field) FROM test WHERE test.id = tab.id), '$'))[1]
|| '-' ||
STRING_AGG(elem, '$'))
FROM
(
SELECT
t.id, x.elem, x.num
FROM test t
LEFT JOIN LATERAL
UNNEST(STRING_TO_ARRAY(REVERSE((SELECT t_field
FROM test
WHERE test.id = t.id
)), '$'))
WITH ORDINALITY AS x (elem, num) ON TRUE
) AS tab
WHERE tab.num > 1
GROUP BY tab.id
LIMIT 2;
Result:
t_field result
bar$foo$john$doe$xxx bar$foo$john$doe-xxx
7a29f$d06f$20e$21f$1b1 7a29f$d06f$20e$21f-1b1 -- will vary by fiddle run!
result
bar$foo$john$doe-xxx
What this is doing is to aggregate the reversed string back to its original form using the $ as the separator, but EXCLUDING the first element (WHERE num > 1;). In place of the first element is the first element - the array reference [1] + the hyphen (|| '-' ||) and so, we have xxx- plus the other elements of the reversed string with $ separating them.
We then just simply apply REVERSE() to the whole construct to give the desired result!
There is a solution possible that doesn't use WITH ORDINALITY (ROW_NUMBER() instead) - see discussion in the individual fiddle.
The performance figures on 10M records on a home VM are shown with each query - the db<>fiddle (30,000 records) results mirror them fairly closely in terms of relative magnitudes.
So, in this scenario, use string based methods if possible, but regular expressions can help reduce the SLOC count, however they can be slower - it's up to the DBA/Dev to make that choice between speed and complexity.
regexp_replaceYou can do this with a regex and a capture paren using regexp_replace
SELECT regexp_replace(t.x, '(.*)\$', '\1-' )
FROM ( VALUES ('bar$foo$john$doe$xxx') ) AS t(x);
Will replace the last $ with -. The end result is,
bar$foo$john$doe-xxx
Here is how it works,
$ into \1 and saves it.$Then it restores the capture with \1 and adds a - leaving the rest of the string in tact.
Yet another Option for you.
Admittedly, this one may or may not be relevant to your situation, but I'll mention it anyway, if only for completeness ...
Properly Normalise your Data to have only one value per field.
That way, changing any one value becomes Childs-Play.
It's just a regular update statement.
Caveat: If you never query your table using any part of this field's value, then this approach is [probably] overkill.
