Here's a sample code to show you how to do it. It uses a cte that creates a tally table on the fly. You can convert the cte into a view, function or store it in a table. I suggest that you read more about tally tables to understand how do they replace loops.

CREATE TABLE #SampleData ( IN_NUM varchar(16));
INSERT INTO #SampleData VALUES('3456738947'), ('41724246874135');

WITH
E(n) AS(
  SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
  SELECT a.n FROM E a, E b
),
E4(n) AS(
  SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
  SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
  FROM E4
)
SELECT IN_NUM, SUBSTRING( IN_NUM, n, 8)
FROM #SampleData s
JOIN cteTally t ON LEN(s.IN_NUM) - 7 >= t.n
--ORDER BY IN_NUM, n
;

GO
DROP TABLE #SampleData