You can use
Common Table Expressions[
^]:
DECLARE @tmp TABLE (s VARCHAR(300))
INSERT INTO @tmp (s)
SELECT '1000*2=2000$500*1=500$100*0=0$50*0=0$20*0=0'
;WITH CTE AS
(
SELECT LEFT(s, CHARINDEX('$',s)-1) AS [Value], RIGHT(s, LEN(s)- CHARINDEX('$',s)) AS [Remainder]
FROM @tmp
WHERE CHARINDEX('$',s)>0
UNION ALL
SELECT LEFT([Remainder], CHARINDEX('$',[Remainder])-1) AS [Value], RIGHT([Remainder], LEN([Remainder])- CHARINDEX('$',[Remainder])) AS [Remainder]
FROM CTE
WHERE CHARINDEX('$',[Remainder])>0
UNION ALL
SELECT [Remainder] AS [Value], NULL AS [Remainder]
FROM CTE
WHERE CHARINDEX('$',[Remainder])=0
)
SELECT *
FROM CTE
Result:
[Value] [Remainder]
1000*2=2000 500*1=500$100*0=0$50*0=0$20*0=0
500*1=500 100*0=0$50*0=0$20*0=0
100*0=0 50*0=0$20*0=0
50*0=0 20*0=0
20*0=0 NULL