SQL - Stack multiple substrings in one column

Question

I am looking to stack multiple substrings in one column. I am looking for alternatives to UNION ALL because my real data is massive and there are over 30 occurrences of sub strings for each row.

Below code parse out the substrings for me but create columns for each sub string. I am looking to stack those smp and smp_Num under one column. I tried using inner join and unpivot but it didn't work for me. I am using DB2.

SELECT Prod_Typ, 
    SUBSTR(Product, 1, 3) as Smp,
        SUBSTR(Product, 4, 4) as Smp_Num,
    SUBSTR(Product, 8, 3) as Smp1,
    SUBSTR(Product, 11, 4) as Smp_Num1,
        SUBSTR(Product, 15, 3) as Smp2,
    SUBSTR(Product, 18, 4) as Smp_Num2,
        SUBSTR(Product, 22, 3) as Smp3,
        SUBSTR(Product, 25, 4) as Smp_Num3,
FROM my_table

My data looks like this.

Prod_Typ	Product
11789	AAD3241HHA3261UUB MNN4567
11790	TTN7689KJI FSS9980

What I am looking for my data to look like:

Prod_Typ	Smp	Smp_Num
11789	AAD	3241
11789	HHA	3261
11789	UUB
11789	MNN	4567
11790	TTN	7689
11790	KJI
11790	FSS	9980

The client you're using doesn't matter, but the database does. From your previous question, which had another approach already, you are using DB2? — Alex Poole
– Alex Poole, Commented May 3, 2024 at 18:55
I would strongly suggest not storing data like this. Combining multiple pieces of meaningful information together into a single column creates all kinds of headaches down the road, even more so when you have repeating groups embedded. You'll be much better off using a child table for those repeating groups, each row of which should break out your data into separate columns each with their own appropriate datatype. — Paul W
– Paul W, Commented May 3, 2024 at 19:07

MT0 · Accepted Answer · 2024-05-03 19:07:20Z

You can use:

WITH positions (prod_typ, product, spos) AS (
  SELECT prod_typ, product, 1
  FROM   my_table
UNION ALL
  SELECT prod_typ, product, spos + 7
  FROM   positions
  WHERE  spos + 7 <= LENGTH(product)
)
SEARCH DEPTH FIRST BY prod_typ SET order_id
SELECT Prod_Typ, 
       SUBSTR(product, spos,     3) AS smp,
       SUBSTR(product, spos + 3, 4) AS Smp_Num
FROM   positions

or, from Oracle 12:

SELECT Prod_Typ, 
       SUBSTR(product, n * 7 - 6, 3) AS smp,
       SUBSTR(product, n * 7 - 3, 4) AS Smp_Num
FROM   my_table
       CROSS JOIN LATERAL (
         SELECT LEVEL AS n
         FROM   DUAL
         CONNECT BY LEVEL <= LENGTH(product)/7
       )

Which, for the sample data:

CREATE TABLE my_table(Prod_Typ, Product) AS
SELECT 11789, 'AAD3241HHA3261UUB    MNN4567' FROM DUAL UNION ALL
SELECT 11790, 'TTN7689KJI    FSS9980' FROM DUAL

Both output:

PROD_TYP	SMP	SMP_NUM
11789	AAD	3241
11789	HHA	3261
11789	UUB
11789	MNN	4567
11790	TTN	7689
11790	KJI
11790	FSS	9980

Oracle fiddle

If you are using DB2 then you can use the first query without the SEARCH clause:

WITH positions (prod_typ, product, spos) AS (
  SELECT prod_typ, product, 1
  FROM   my_table
UNION ALL
  SELECT prod_typ, product, spos + 7
  FROM   positions
  WHERE  spos + 7 <= LENGTH(product)
)
SELECT Prod_Typ, 
       SUBSTR(product, spos,     3) AS smp,
       SUBSTR(product, spos + 3, 4) AS Smp_Num
FROM   positions

DB2 fiddle

Collectives™ on Stack Overflow

SQL - Stack multiple substrings in one column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related