Reputation: 701
Can any one help me in solving this. In a table, I have the data like this right now.
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
Thanks in advance
Upvotes: 0
Views: 9481
Reputation: 60462
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...
Upvotes: 8
Reputation: 701
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
Upvotes: 0
Reputation: 9607
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
...
Upvotes: 1