Binary Replace

Binary replace (replace for bytea)

Library Snippets

binary_replace

Works with PostgreSQL

9.0+

Written in

PL/pgSQL

Depends on

Nothing

This function is the equivalent for binary strings (bytea) of the built-in function for text: replace(string text, from text, to text)

It searches occurrences of s1 into str and replaces them by s2.

CREATE FUNCTION binary_replace(str bytea, s1 bytea, s2 bytea)
  RETURNS bytea as $$
DECLARE
 i int:=position(s1 in str);
 j int;
 l1 int:=length(s1);
 l2 int:=length(s2);
BEGIN
  while (i>0) loop
    str:=overlay(str placing s2 from i for l1);
    j:=position(s1 in substring(str from i+l2));
    if (j>0) then
      i:=i+j-1+l2;
    else
      i:=0;
    end if;
  end loop;
  return str;
END
$$ language plpgsql immutable;