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

Posted by in PostgreSQL category on for Beginner level | Points: 250 | Views : 7993 red flag
Rating: 4 out of 5  
 1 vote(s)

In this article, we will look into the PostgreSQL's "Overlay" function and it's equivalent implementation in Sql Server 2012.


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.


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


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

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

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

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

	-- Return the result of the function
	RETURN @OutputString



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


String Functions and Operators



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

Page copy protected against web site content infringement by Copyscape

About the Author

Full Name: Niladri Biswas
Member Level: Platinum
Member Status: Member
Member Since: 10/25/2010 11:04:24 AM
Country: India
Best Regards, Niladri Biswas
Technical Lead at HCL Technologies

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)