Looping through values stripping 1 character at a time

I want to loop through values and strip 1 character at a time from the values and display the result.

So if I have a table with values:

ID
___
34679
13390
89906

I want the result to look like this

Id
----
4679
679
79
9
3390
390
90
0
9906
906
06
6

Please don't use loops for things like this (I'd also reserve recursive CTEs for scenarios where you have much less control over things, like hierarchies). Loops are bad in SQL; SQL is optimized to work in sets.

DECLARE @foo TABLE(ID INT);

INSERT @foo VALUES(34679),(13390),(89906);

;WITH x AS 
(
  SELECT TOP (2048) n = ROW_NUMBER() OVER (ORDER BY Number)
  FROM master.dbo.spt_values ORDER BY Number
)
SELECT RIGHT(f.ID, x.n) FROM x
INNER JOIN @foo AS f
ON x.n < LEN(f.ID);

Results:

9
79
679
4679
0
90
390
3390
6
06
906
9906

• http://sqlperformance.com/generate-a-set-1

• http://sqlperformance.com/generate-a-set-2

• http://sqlperformance.com/generate-a-set-3

StackExchange Database Administrators Q#64359, answer score: 19