all substrings of a specified length

I have a table with a string column with variable length. I need to get all the substrings of the cell value with a specified length

Example:

094740

Statement:

select ???(Column, 3) from table

Result to be:

094
947
474
740

Can this be achieved without a stored procedure

You can try to use CTE recursive with a simple math formula to make it.

CTE recursive will get a result set to represent how many row with offset by the statement

Query 1:

declare @offset int = 3
;WITH CTE AS (
   SELECT 0 startIndex, len(col) totalLen,col
   FROM T
   UNION ALL
   SELECT startIndex + 1,totalLen,col
   FROM CTE
   WHERE startIndex <= totalLen - @offset 
)
SELECT substring(col,startIndex, @offset) 
FROM CTE
WHERE startIndex > 0

Results:

|     |
|-----|
| 094 |
| 947 |
| 474 |
| 740 |

You have two possible options - a recursive CTE or a statement using a number table:

Table:

SELECT *
INTO StringTable
FROM (VALUES ('094740'), ('1237')) v (StringColumn)

Statement using a recursive CTE:

DECLARE @Length int = 3
;WITH rCTE AS (
    SELECT 1 AS Position, t.StringColumn
    FROM StringTable t   
    UNION ALL
    SELECT r.Position + 1, r.StringColumn
    FROM rCTE r
    WHERE r.Position + @Length <= LEN(r.StringColumn)
)
SELECT StringColumn, SUBSTRING(StringColumn, Position, @Length) AS Substring
FROM rCTE
ORDER BY StringColumn, Position

Statement using a number table (with 1000 rows):

DECLARE @Length int = 3
;WITH NumberTable AS (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Rn
    FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) a (n)
    CROSS APPLY (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) b (n)
    CROSS APPLY (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) c (n)
)
SELECT t.StringColumn, SUBSTRING(t.StringColumn, n.Rn, @Length) AS Substring
FROM StringTable t
JOIN Numbertable n ON (n.Rn + @Length - 1) <= LEN(t.StringColumn)
ORDER BY t.StringColumn, n.Rn

Result:

StringColumn Substring
----------------------
094740       094
094740       947
094740       474
094740       740
1237         123
1237         237