Peformance: time for 10M records = 14298.643 ms.ģ: Alternative regex with REGEXP_REPLACE() (doesn't use REVERSE() - see Evan Carroll's answer (individual fiddle)): SELECT.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. It is always worthwhile trying to find a different solution with the non-regex functionality first ( c.f.
#Postgresql replace characters between code#
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! Unfortunately (paraphrasing), with great power comes great complexity. 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. 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. Pretty fast, but iterating on the replacements is very costly.This problem involves a bit of lateral thinking. In a plpgsql implementation, searching with the alternation appears to be (tested up to 1000, knowing that they get combined into a single regularĮxpression) and many actual replacements occurring. This Perl function appears to be very fast, even with many strings to replace These fiveĬREATE FUNCTION multi_replace ( string text, orig text, repl text ) RETURNS text AS $BODY $ my ( $string, $orig, $repl ) = my %subs if ( != ) /g return $string $BODY $ language plperl strict immutable Job, and it seems the popular answer when searching forĪ solution on the web, but in the general case, they produce flawed results ifĪny inner replacement happens to create a match for other outerĪs an example, consider the necessary conversion of characters into HTMLĮntities when creating an HTML fragment from a raw text. In some cases, nested or successive replace() calls might do the Regexp_replace(string, 'foo|bar', 'baz', 'g') would replace bothįoo and bar by baz, but it cannot be instructed to replace foo by Multiple strings in a single pass, but it can’t do multiple In PostgreSQL, regexp_replace does support alternations to search for Or the substitutions operators with regular expressions in Python or Perl Multiple strings each by its own independent replacement, like for Select replace('the string is bar', 'bar', 'foo') īut there isn’t a PostgreSQL core function to substitute
0 kommentar(er)
