Showing posts with label Replace last occurrence of a string in SQL Server?. Show all posts
Showing posts with label Replace last occurrence of a string in SQL Server?. Show all posts

Replace last occurrence of a string in SQL Server?

Step 1: Create a procedure in order to replace the last occurrence of a string with a specified string as shown below:

CREATE PROC sp_ReplaceStringWithLastOccurence
(
      @Texto varchar(MAX),
      @findString VARCHAR(100),
      @replaceString VARCHAR(100)
)
AS
BEGIN
    DECLARE @Pos INT=0,@startPos INT=0,@endPos INT=0,@totalStringLength INT=LEN(@Texto),@Splitter CHAR(1)=' ',@initialString
VARCHAR(MAX)=@Texto
  DECLARE @Lista TABLE (
      rowData VARCHAR(8000),
      startPos INT,
      endPos INT
  )
  WHILE LEN(@Texto)>0
  BEGIN
      SET @startPos=@startPos+@Pos
      SET @Pos = PATINDEX('%'+@Splitter+'%',@Texto)
      SET @endPos=@Pos-1

      IF @Pos > 0
      BEGIN
        INSERT INTO @Lista(rowData,startPos,endPos)
        SELECT left(@Texto, @Pos-1),@startPos+1,@endPos

        SET @Texto = right(@Texto, LEN(@Texto)-@Pos)
      END
      ELSE
      BEGIN
        DECLARE @end INT=0
        set @end=@totalStringLength+1-@startPos

        INSERT INTO @Lista(rowData,startPos,endPos)
        SELECT @Texto,@startPos+1,@end

        SET @Texto = ''
      END
    END

--Break the input string into by space using the split function
DECLARE @startPos1 INT=0,@endPos1 INT=0
SELECT TOP 1
@startPos1=startPos,
@endPos1=startPos+endPos
FROM
@Lista
WHERE
rowData=@findString
ORDER BY
startPos DESC

--Replace the replace string with last occurence of find string
SELECT SUBSTRING(@initialString,0,@startPos1) +@replaceString+ SUBSTRING(@initialString,@endPos1,@totalStringLength) as 'Result'

END
GO

Step 2: Call the SP created above and pass the string in which we need to search the last occurrence of a string & replace with a specified string as shown below:

exec sp_SplitToGetLastOccurence 'is hello is is world this world is so beautiful is','is','YES'

Step 3: The Output will be as shown below:




Followers

Popular Posts