Netezza: transpose delimited string into table rows e.g. split delimited character field into records

Related posts: 

• Netezza: Split delimited string how to
• Netezza: concatenate rows into string with XMLAGG()
• Netezza: create UDX concatenating row values into coma delimited string GROUP_CONCAT

Have this

Need this

SOLUTION:  This will transpose delimited record to table rows

SELECT c.cnt                                                           AS POSITION_IN_STRING_ARRAY

      ,SQLX..REGEXP_EXTRACT(a.longstring,'[^|]+',1,cast(c.cnt as int)) AS GET_VALUE_EVERY_ELEMENT

FROM (

              select '201|202|203|204|205|206|207|208|209|210|211|212|213|214|215' as longstring

       ) a

cross join (select row_number () OVER (order by 1) as cnt

              from (SELECT 1 FROM HISTDB.."$hist_log_entry_3" LIMIT 70) v

           )c

WHERE GET_VALUE_EVERY_ELEMENT IS NOT NULL

---

EXAMPLE AND DETAILS : 

This is real problem. Split pipe delimited string into individual elements: one table record for each element.

SOLUTION #1: Using  Netezza SQL Toolkit array functions  array_split () and get_value_varchar () .

Proof of concept: 

SELECT

 sqlx..array_split(STR,'|')                                 as THIS_IS_ARRAY

,sqlx..get_value_varchar(sqlx..array_split(STR,'|'), NULL)  as GET_NULL

,sqlx..get_value_varchar(sqlx..array_split(STR,'|'),  1  )  as GET_01  --ARR starts w. 1

,sqlx..get_value_varchar(sqlx..array_split(STR,'|'),  2  )  as GET_02

,sqlx..get_value_varchar(sqlx..array_split(STR,'|'), 15  )  as GET_15

,sqlx..array_count(sqlx..array_split(STR,'|'))              as ARRAY_COUNT

--,sqlx..get_value_varchar(sqlx..array_split(STR,'|'), 16 ) as GET_NONEXIST 

--- ERROR [HY000] ERROR:  0 : Invalid Index Id

--,sqlx..get_value_varchar(sqlx..array_split(STR,'|'), '1') as GET_STRING   

--- ERROR [HY000] ERROR:  0 : Invalid Index Name

  from (

       select '201|202|203|204|205|206|207|208|209|210|211|212|213|214|215' as STR) AS F

;

Putting this all together: 

select F.ROWID AS RECID                    --- Original source records PK or unique identifier.            

,c.CNT        AS POSITION_IN_STRING_ARRAY --- Position of elemen in the field array (optional).

,sqlx..get_value_varchar(ARR,CAST(DECODE(SIGN(LEN-c.CNT),1,c.CNT,null) AS INT) ) AS GET_VALUE_EVERY_ELEMENT  

FROM  --- CAST is a must otherwise GET_VALUE_VARCHAR fails with:  "ERROR: Invalid Index Name"

(select ROWID                                                          --- Get ROWID or replace it with PK column

      ,sqlx..array_split(LONGSTRING,'|')                    AS ARR   --- Parse string into array only once

      ,sqlx..array_count(sqlx..array_split(LONGSTRING,'|')) AS LEN   --- Get lenth of this field to be used later

  from TESTDB.LONGSTRING

) AS F  --- Generate sequence from 1..N. Where N is the longest array in the field CNTRCT_SPEED

cross join (select row_number () OVER (order by 1) as cnt             

                 FROM (SELECT 1 FROM TESTDB.LONGSTRING limit 70 ) v

) AS C --- Eleminate non-existing array elements. They still in Cartesian Product (1..70) where there is no match.

where DECODE(SIGN(LEN-c.CNT),1,c.CNT,null) is not null

;

---

SOLUTION #2: Using  Netezza SQL Toolkit regular expression function regexp_extract () .

Proof of concept: 

 SELECT

 sqlx..regexp_match_count(STR,'[^|]+')      as MATCH_COUNT

,sqlx..regexp_extract(STR,'[^|]+',1,NULL)   as GET_NULL

,sqlx..regexp_extract(STR,'[^|]+',1,1)      as GET_01

,sqlx..regexp_extract(STR,'[^|]+',1,2)       as GET_02

,sqlx..regexp_extract(STR,'[^|]+',1,15)     as GET_15

,sqlx..regexp_extract(STR,'[^|]+',1,16)     as GET_GET_16

,sqlx..regexp_extract(STR,'[^|]+',1,222)    as GET_NONEXIST

---,sqlx..regexp_extract(STR,'[^|]+',1,0)   as GET_ZERO   

--- ERROR [HY000] ERROR:  0 : Regex: Illegal Reference Value 0

---,sqlx..regexp_extract(STR,'[^|]+',1,'1') as GET_STRING 

--- ERROR [HY000] ERROR:  0 : Regex: Illegal Option (1)

  from (

       select '201|202|203|204|205|206|207|208|209|210|211|212|213|214|215' as STR) AS F

;

Putting this all together: 

select a.ROWID AS RECID                  --- Original source records PK or unique identifier. 

      ,c.cnt AS POSITION_IN_STRING_ARRAY --- Position of elemen in the field array (optional).

      ,sqlx..regexp_extract(LONGSTRING,'[^|]+',1,cast(c.cnt as int)) GET_VALUE_EVERY_ELEMENT

from (select ROWID, * from TESTDB.LONGSTRING

) a --- Generate sequence from 1..N. Where N is the longest array in the field CNTRCT_SPEED

cross join (select row_number () OVER (order by 1) as cnt    

                 FROM (SELECT 1 FROM TESTDB.LONGSTRING  limit 70 ) v ) c

--- Eliminate non-existing elements. They still listed in Cartesian Product (1..70) where there is no match.

WHERE GET_VALUE_EVERY_ELEMENT IS NOT NULL

;

---

PERFORMANCE COMPARISON:

Notice,  limiting depth of search in crossjoin  ... limit70 will reduce the data explosion and improve performance.  This is tested at TF6 with 14 million record original source table.

SOLUTION #1:  array_split ()  and get_value_varchar () .

/*  LIMIT 5,000,000 Duration 19.1540956 sec. Records Affected:   6,605,274. */ /* LIMIT 10,000,000 Duration 52.2369878 sec. Records Affected:  43,817,113. */ /* LIMIT 12,000,000 Duration 45.5606059 sec. Records Affected:  79,234,929.*/ /* LIMIT 13,000,000 Duration 49.7078431 sec. Records Affected:  98,032,515. */ /* LIMIT 14,000,000 Duration 50.1268671 sec. Records Affected: 106,848,663.*/

SOLUTION #2:  regexp_extract () .

/* LIMIT  5,000,000 Duration  131.9048611 sec. Records Affected:    6,605,274. */ /* LIMIT 10,000,000 Duration  683.4229685 sec. Records Affected:   43,817,113. */ /* LIMIT 12,000,000 Duration 1175.0658919 sec. Records Affected:   79,234,929. */ /* LIMIT 13,000,000 Duration 1433.8532871 sec. Records Affected:   98,032,515. */ /* LIMIT 14,000,000 Duration 1562.2020755 sec. Records Affected:  106,848,663. */