It is worth bearing in mind that regular expressions are incredibly powerful. Then, the final step is to reverse our edited string back to its original order and we have the result! 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. Note also that $ is a regex meta-character - i.e. , '-', 'g'), then all of the dollars would be replaced - and you can do that anyway with the (much cheaper) REPLACE() function.
POSTGRESQL SUBSTRING FROM END CODE
Note that this will only replace the first instance of $ because the 'g' (global) flag isn't present - if the code read. Run REGEXP_REPLACE('xxx', '\$', '-') on the reversed string. What is being done (from the inside out) is: Peformance: time for 10M records = 8034.787 ms.Ģ: Reverse() and the regex function REGEXP_REPLACE() (individual fiddle): SELECT.LENGTH(t_field) + 1 - STRPOS(REVERSE(t_field), '$') 1: Postgresql string functions (see the manual), uses OVERLAY(), STRPOS() AND REVERSE() (individual fiddle): SELECT You may vary this limit on the fiddle to check. replacing the last dollar ( $) sign with a hyphen ( -). 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. It was tested on both dbfiddle 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. The solutions will be presented in order of performance. The home test VM used 10,000,000 records - 16 GB RAM, 1 CPU, SSD GENERATE_SERIES(1, 29999) -fiddle server too hard! VALUES ('bar$foo$john$doe$xxx') - OP's own data Then we run the following queries to populate the table - the first record is the OP's own data - the rest is randomly generated! The PRIMARY KEY is only required by the 5th solution. Id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, An individual fiddle with each solution separately is included with each solution below): CREATE TABLE test 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.įor all of the 5 solutions presented, we have the following (a fiddle with all the code below is available here. This problem involves a bit of lateral thinking.
0 kommentar(er)
