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

Niladri.Biswas
Posted by in PostgreSQL category on for Beginner level | Points: 250 | Views : 3998 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.

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

String Functions and Operators

 

Conclusion

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

Niladri.Biswas
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
http://www.dotnetfunda.com
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)