Split The Column which is delimited into separate Rows in Teradata 14

Question

Can any one help me in solving this. In a table, I have the data like this right now.

enter image description here

How do i split the column Nodes which has delimiter TTBFA-TTBFB-TTBFC-TTBFD into 4 rows with other columns being same.

california region GAXAEB 102,520,000 18.71 4 8/30/2014

california region TTBFA 92,160,000 23.33 3 9/13/2014

california region TTBFB 92,160,000 23.33 3 9/13/2014

california region TTBFC 92,160,000 23.33 3 9/13/2014

california region TTBFD 92,160,000 23.33 3 9/13/2014

The value for column NODES is not always 5 characters , It may vary like below

enter image description here

Thanks in advance

dnoeth · Answer 1 · 2014-11-21T07:24:08.980

The function INSTR usually implies you're running TD14+.

There's also the STRTOK function, better use this instead of SUBSTRING(INSTR).

And instead of up to 15 UNION ALLs you can also cross join to a table with numbers:

SELECT region_name, STRTOK(nodes, '-', i) AS x
FROM table
CROSS JOIN
 ( -- better don't use sys_calendar.CALENDAR as there are no statistics on day_of_calendar
   SELECT day_of_calendar AS i
   FROM sys_calendar.CALENDAR
   WHERE i <= 15
 ) AS dt
WHERE x IS NOT NULL

And you can utilize STRTOK_SPLIT_TO_TABLE in TD14, too:

SELECT * 
FROM table AS t1
JOIN 
(
   SELECT * 
   FROM TABLE (STRTOK_SPLIT_TO_TABLE(table.division, table.nodes, '-')
      RETURNS (division VARCHAR(30) CHARACTER SET UNICODE
              ,tokennum INTEGER
              ,token VARCHAR(30) CHARACTER SET UNICODE)
              ) AS dt
 ) AS t2
ON t1.division = t2.division

Hopefully this is for data cleansing and not for daily use...

This should be the accepted answer ! Thank you dnoeth ! – Pholochtairze May 24 '16 at 15:10 — Pholochtairze, May 24 '16 at 15:10

Beth · Accepted Answer · 2014-11-20T17:19:06.160

1

you could use (whatever number is your max number of nodes) UNION ALL statements and SUBSTRING with INSTR for the possible locations for a node

try something like:

        SELECT region_name, nodes AS node, 
               sgspeed, sgutil, portCount, WeekendingDate
        FROM t
        WHERE instr(nodes,'-') = 0
        UNION ALL
        SELECT region_name, SUBSTRING(nodes  FROM instr(nodes,'-',1,1) +1 FOR instr(nodes,'-',1,2)-1) AS node, 
               sgspeed, sgutil, portCount, WeekendingDate
        FROM t
        WHERE instr(nodes,'-') > 0
        UNION ALL
        SELECT region_name, SUBSTRING(nodes  FROM instr(nodes,'-',1,2) +1 FOR instr(nodes,'-',1,3)-1) AS node, 
               sgspeed, sgutil, portCount, WeekendingDate
        FROM t
        WHERE instr(nodes,'-',1,2) > 0
        UNION ALL
        SELECT region_name, SUBSTRING(nodes  FROM instr(nodes,'-',1,3) +1 FOR instr(nodes,'-',1,4)-1) AS node, 
               sgspeed, sgutil, portCount, WeekendingDate
        FROM t
        WHERE instr(nodes,'-',1,3) > 0
    ...

edited Nov 20 '14 at 17:19

answered Nov 20 '14 at 16:14

Beth

9,531
1
24
43

Hi Beth The column Nodes is not always 5 characters , This is just an example . It can vary from column to column. – Naveen Nov 20 '14 at 16:25
is the dash delimeter the same? like AA-BBB-CCCC-DDDDD – Beth Nov 20 '14 at 16:29
Yes dash is the delimiter for all the data. – Naveen Nov 20 '14 at 16:32
I think you'll need Instr, still with multiple UNIONs. Do you know the max number of nodes in the nodes column? – Beth Nov 20 '14 at 17:08
Hi Beth , It is a varchar(500) field. The max is see is 15 nodes in a column with delimiter. – Naveen Nov 20 '14 at 17:22
yeah, I can't test the syntax without the schema, etc. Glad it works! – Beth Nov 20 '14 at 19:41

score 0 · Answer 3 · edited Nov 21 '14 at 04:30

This is working fine.

select  
REGION_NAME,
case when POSITION('-' IN Nodes) = 0  then NODES else SUBSTRING(Nodes,0,POSITION('-' IN Nodes)) end as node, 
SgSpeed, 
SgUtil,
PortCount, 
WeekEndingDate 
FROM table

UNION 

select
REGION_NAME,
SUBSTRING(nodes  FROM instr(nodes,'-',1,1)+1 for instr(nodes,'-',1,1)-1) AS node, 
SgSpeed, 
SgUtil,
PortCount, 
WeekEndingDate
FROM table
WHERE instr(nodes,'-') > 0

UNION

select
REGION_NAME,
SUBSTRING(nodes  FROM instr(nodes,'-',1,2)+1 for instr(nodes,'-',1,1)-1) AS node, 
SgSpeed, 
SgUtil,
PortCount, 
WeekEndingDate
FROM table
WHERE instr(nodes,'-') > 0

UNION

select
REGION_NAME,
SUBSTRING(nodes  FROM instr(nodes,'-',1,3)+1 for instr(nodes,'-',1,1)-1) AS node, 
SgSpeed, 
SgUtil,
PortCount, 
WeekEndingDate 
FROM table
WHERE instr(nodes,'-') > 0

Split The Column which is delimited into separate Rows in Teradata 14

3 Answers3

Linked