How can I replace duplicate strings with increasing order in T-SQL?

Question

I have a single row table:

Id | Description
---------------
 1   #Hello#, Its 5 am. #Hello#, Its 9 am. #Hello# its 12 pm.

I want to replace these duplicate string #Hello# with an increasing order. I need output like

Id | Description
---------------
 1   #Hello#, Its 5 am. #Hello1#, Its 9 am. #Hello2# its 12 pm

Answer 1

Try this one,

DECLARE @V_STR      NVARCHAR(1000)  =   (SELECT [Description] FROM [Table1])
        ,@V_COUNT   INT =   0
        ,@V_TMP     NVARCHAR(100)   =   '#Hello#'
WHILE ((CHARINDEX(@V_TMP,@V_STR)) > 0)
BEGIN
    SELECT @V_STR = STUFF(@V_STR,(CHARINDEX(@V_TMP,@V_STR)),LEN(@V_TMP),'#Hello'+CAST(@V_COUNT AS NVARCHAR)+'#')
    SET @V_COUNT += 1
END
SELECT @V_STR