Hi,
Try this
DECLARE @STR VARCHAR(100)
SET @STR = 'ABC***PQR***XYZ***FIRST NAME***MOBILE NO.'
;with cte as
(
Select CAST( '<i>'+replace (@STR ,'***','</i><i>')+'</i>' as XML) as word
)
Select x.i.value ('.','nvarchar(100)') as Items
from cte
cross apply word.nodes('/i') as X(i)
output
Items
ABC
PQR
XYZ
FIRST NAME
MOBILE NO.