Creating multiple Rows from one row by substringing a column

Question

Creating multiple Rows from one row by substringing a column

lsinoff

Valued Member

Points: 58
More actions
January 2, 2018 at 9:33 pm

#334319

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

gbritton1 SSCertifiable Points: 6522 More actions · Answer 1

lsinoff - Tuesday, January 2, 2018 9:33 PM
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.

lsinoff Valued Member Points: 58 More actions · Answer 2

I do not know enough TSQL to code the loop needed to generate to create the three rows from 1 row as shown in my example.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

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

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

lsinoff - Tuesday, January 2, 2018 9:33 PM
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

I'm a bit confused. Ignoring the typo in Row2 of the output, what are you actually looking for the given value of '3456738947'. Is it just 3 rows as you have listed or do you really want 8 rows using the full rotation of the "ring counter" that you've described?

How about the example of '41724246874135' that Luis used? Do you want just the first 3 rows from the "ring" or do you want the full "ring" of 14 rows?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

lsinoff Valued Member Points: 58 More actions · Answer 5

Just the three rows. Some accounts are sent in by our clients with prefixes and/or suffixes and some of the accounts in our Data Base also have prefixes and/or suffixes. A match on any 7 consecutive characters, same last name and same date of birth will be considered an good match. I need to extract all the 7 character consecutive strings from our DB.