Binary Replace

From PostgreSQL wiki

Jump to: navigation, search

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;
Personal tools