SQL Server: Replace specific character at specified position

In the present world there will be requirements in most of the processes which should execute more dynamically. This is one such scenario. There are many other in this blog related to this.
Here I needed to replace a character in specified position with specific character. I will repeat again:
"Replace a character" - "In specific position" - "with specific character".
This function helps to achieve dynamically.
CREATE FUNCTION fnReplaceCharAtSpecifiedPos(@Str varchar(8000), @Pos int, @Chr char(1))
RETURNS varchar(1000) AS
BEGIN
declare @Res varchar(1000)
set @Res=left(@Str,@Pos-1) + @Chr + right(@Str,len(@Str)-@Pos)
return @Res
END

The above function can be used as
select '123456789',dbo.fnReplaceCharAtSpecifiedPos('123456789',3,'')

select '123456789',dbo.fnReplaceCharAtSpecifiedPos('123456789',6,'x')


We can use STUFF function as well.
SELECT '123456789',STUFF('123456789', 3,1,'x');

No comments:

Post a Comment