Let us implement PostgreSQL's Overlay function in SQL Server 2012

Introduction

PostgreSQL is a powerful open source RDMBS and is a good repository of a handful of functions. In this article, we will look into the "Overlay" function and it's equivalent implementation in Sql Server 2012.

Purpose

To replace substring from a specified position to a specified position.

Syntax

Overlay(string placing string from int [for int])

Example of Overlay function in PostgreSQL

Select Overlay('www.dotnetanda.com' Placing 'funda' From 11 For 4) "Overlay Example"
/* Result */

Overlay Example
---------------
www.dotnetfunda.com

As can be figure out that "anda" has been replaced by "funda". The replacement started from position 11 and should continue till 4 since the length of "anda" is 4.So the characters between 11 to 15 (11+4) are "anda" and those should be replaced by "funda". Though we don't have in our SQL Server such a function, but we can always create our own. Here is my attempt

Overlay function in Sql Server 2012

CREATE FUNCTION [dbo].[Overlay]
(
   -- Add the parameters for the function here
   @InputString varchar(4000)
   ,@ReplaceString varchar(100)
   ,@From int
   ,@For int
)
RETURNS VARCHAR(4000)

AS
BEGIN
	-- Declare the return variable here
	Declare @OutputString   VARCHAR(4000)

	Select  @OutputString = Stuff(@InputString,@From,@For,@ReplaceString) 

	-- Return the result of the function
	RETURN @OutputString

END

Usage

Declare @Initialstr varchar(100) = 'www.dotnetanda.com'
Declare @Replacestr varchar(100) = 'funda'
Declare @from int = 11
Declare @for int = 4

Select dbo.Overlay(@Initialstr,@Replacestr,@from,@for)  "Overlay Example"

/*Result */
Overlay Example
---------------
www.dotnetfunda.com

References

Conclusion

Hope this will be a useful utility function in SQL parlance and this article will be useful. Thanks for reading