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