I need to create from 1 to n rows from a single row where the one column in the read table contains a string from 8 to 16 characters long. The rows written contain a single column 8 bytes long containing all each row being a different combination of 8 consecutive characters from the column of the read table.
INPUT: IN_NUM contains '3456738947'
Output Row 1: NUM_7 contains '34567389'
Output Row 2: NUM_7 contains '45673894'
Output Row 3: NUM_7 contains '56738947'
My ultimate input table will have over 1,000,000 rows and many more columns
My ultimate output table will have about 4,000,000 rows and a subset of columns from the input table
This is pretty easy to do with SUBSTRING and some randomization combined with CROSS APPLY. I don't understand the 4 million output rows, though. Are there not 8! combinations of the 8 bytes? That's a pretty big number. Also, what's the business motivation for this? There's may a better way.