DECLARE @Numbertable table

(

ID INT PRIMARY KEY

)

INSERT INTO @Numbertable

select TOP 1000 ROW_NUMBER() OVER(order by si.object_id)

from sys.objects si,sys.objects s

DECLARE @separatolog TABLE

(

SeparatorLogID INT,

ID INT IDENTITY(1,1) PRIMARY KEY

)

DECLARE @STR varchar(100) = 'ABCDEABCDEABCDEABCDEABCDEABCDEABCDEABCDEABCDE'

DECLARE @Separator VARCHAR(10) = ''

DECLARE @ValueAdd VARCHAR(10) = 'DEA'

INSERT INTO @separatolog

select distinct CHARINDEX(@ValueAdd,@str,ID)

from @Numbertable

where ID <= LEN(@str) and CHARINDEX(@ValueAdd,@str,ID) > 0

ORDER BY 1

select ID AS 'OCCURENCE',SeparatorLogID AS 'Exact Location' from @separatolog