拆分 Teradata 14 中分隔为单独行的列 [英] Split The Column which is delimited into separate Rows in Teradata 14

问题描述

谁能帮我解决这个问题.在一个表中，我现在有这样的数据.

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

如何将具有分隔符 TTBFA-TTBFB-TTBFC-TTBFD 的列节点拆分为 4 行，其他列相同.

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

加州地区 GAXAEB 102,520,000 18.71 4 8/30/2014

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

加州地区 TTBFA 92,160,000 23.33 3 9/13/2014

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

加州地区 TTBFB 92,160,000 23.33 3 9/13/2014

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

加州地区 TTBFC 92,160,000 23.33 3 9/13/2014

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

加州地区 TTBFD 92,160,000 23.33 3 9/13/2014

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

列 NODES 的值并不总是 5 个字符，它可能会变化如下

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

提前致谢

推荐答案

you can use (whatever number is your max number of nodes) UNION ALL 语句和 SUBSTRING withINSTR 节点的可能位置

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

尝试类似:

        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
    ... 

这篇关于拆分 Teradata 14 中分隔为单独行的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！