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

• gbritton1

  SSCertifiable

  Points: 6522

  More actions

  January 3, 2018 at 7:17 am

  #1974220

  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

  January 3, 2018 at 7:54 am

  #1974229

  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

  January 3, 2018 at 8:12 am

  #1974234

  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: 1004589

  More actions

  January 3, 2018 at 3:56 pm

  #1974364

  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

  January 3, 2018 at 5:50 pm

  #1974372

  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.