Question 305467 in PostgreSQL Question posted in
The official documentation can be found here.

How to replace multiple string values from a string using single regexp_replace function in postgresql

I have string like fd_val__pharmacy_1_2_3_text

In that above string i want replace

  1. double underscore with single underscore
  2. replace fd_ with blank
  3. replace _1, _2, _3 with blank.

I am tried by using Replace function multiple times. But i want to do it by using regexp_replace function only once. Is it possible. Please help me with the query.

Output of the query should be val_pharmacy_text

Tags:

2

Answers


  1. 0

    To get the desired output as stated above you can use the following query to solve it.The query is as follows:

    SELECT
  regexp_replace(
    '_val_pharmacy_1_2_3_text',
    '^_+|_+[0-9]+|_+$',
    '',
    'g'
  ) AS replaced_string;

    sample illustration and implementation can be found here: https://dbfiddle.uk/93OllCaa

    Hope this helps

    Login or Signup to reply.
  2. 0

    As of Postgres 16, there’s no built-in function that generically does multiple replacements in a single pass.
    The Perl substitution operator provides an efficient way do it, though. I use the following plperl function:

    /*
  Substitute substrings within a larger string, with Perl s// operator,
  in a single pass. Each element in @orig found in @string (scanned left
  to right) is replaced by the element at the same index in @repl.
  When multiple strings in the array match simultaneously, the longest one
  wins.
*/
CREATE OR REPLACE FUNCTION multi_replace(string text, orig text[], repl text[])
RETURNS text
AS $BODY$
  my ($string, $orig, $repl) = @_;
  my %subs;

  # Check that the arrays are of the same size, unidimensional,
  # and contain no null values.
  if (@$orig != @$repl) {
     elog(ERROR, "array sizes mismatch");
  }
  if (ref @$orig[0] eq 'ARRAY' || ref @$repl[0] eq 'ARRAY') {
     elog(ERROR, "multi-dimensional arrays are not allowed");
  }
  if (grep { !defined } (@$orig, @$repl)) {
     elog(ERROR, "null elements are not allowed");
  }

  # Each element of $orig is a key in %subs to the element at the same
  # index in $repl
  @subs{@$orig} = @$repl;

  # Build a regexp of the form (s1|s2|...)
  # with the substrings sorted to match longest first
  my $re = join "|", map quotemeta,
     sort { (length($b) <=> length($a)) } keys %subs;
  $re = qr/($re)/;

  # The order will be kept in matching because (from perlre):
  # "Alternatives are tried from left to right, so the first alternative
  # found for which the entire expression matches, is the one that is
  # chosen"

  $string =~ s/$re/$subs{$1}/g;
  return $string;

$BODY$ language plperl strict immutable;

    Example:

    select multi_replace(
  'fd_val__pharmacy_1_2_3_text',
  '{__,fd_,_1,_2,_3}',
  '{_, "", "", "", ""}'
);

   multi_replace   
-------------------
 val_pharmacy_text

    You may find more details on the problem statement and implementation in that blog post: Multiple strings replacement with plperl.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search