STUFF Function - SQL Server to PostgreSQL Migration

In SQL Server, the STUFF function inserts a string into another string at a specified position, deleting a specified number of characters.

In PostgreSQL you can use the INSERT function. See also STUFF and SELECT FOR XML PATH conversion.

SQL Server:

  -- Replace 3 characters starting at position 2 with '#'
  SELECT STUFF('abcdef', 2, 3, '#');
  /* a#ef */
 
  -- Remove substring without inserting new substring 
  SELECT STUFF('abcdef', 2, 3, '');
  /* aef */
 
  -- Insert substring without removing characters
  SELECT STUFF('abcdef', 2, 0, '#');
  /* a#bcdef */

PostgreSQL:

  -- Replace 3 characters starting at position 2 with '#'
  SELECT OVERLAY('abcdef' PLACING '#' FROM 2 FOR 3);
  /* a#ef */
 
  -- Remove substring without inserting new substring 
  SELECT OVERLAY('abcdef' PLACING '' FROM 2 FOR 3);
  /* aef */
 
  -- Insert substring without removing characters
  SELECT OVERLAY('abcdef' PLACING '#' FROM 2 FOR 0);
  /* a#bcdef */

For more information, see SQL Server to PostgreSQL Migration.