CODEWITHSUNDEEP
sqlteradata
John
John

Reputation: 195

Teradata sql Split undefined delimiter number

I have a table p

  id_people  age 
     1       22
     2                 --->   (empty)
     3      (10,20)

I want to get result like this 

  id_people  age 
      1       22
      3       10
      3       20

I have tried this

    select t.id_people, 
    STRTOK(t.age,',',1) AS COL_1,STRTOK(t.age,',',2) AS COL_2 from 
(select id_people,age from p  where LENGTH(age) >0 ) t

First of all , it works but i still have the brackets. how can i delete brackets ? Second question : It works fine because i know i have only one comma in a row, if i didn't know the number of comma in a row for column age, i wouldn't be able to handle by this way. How can I use a kind of loop for a situation like that? Example : 

 id_people    age 
   1        (17,18,19,20,21,22,23,24,25,....)
   2        (30,31,32)
   3         -->    (empty)

Thank you

Upvotes: 0

Views: 1434

Answers (1)

dnoeth
dnoeth

Reputation: 60462

Besides STRTOK there's also STRTOK_SPLIT_TO_TABLE :-)

The syntax is a bit unusual:

WITH cte (id_people, age) AS
 (
   SELECT id_people, age FROM dropme
 )
SELECT * 
FROM TABLE
 ( STRTOK_SPLIT_TO_TABLE( cte.id_people , cte.age, '(),') 
   RETURNS ( id_people INT , TokenNum INT , Token VARCHAR (10) CHARACTER SET UNICODE ) 
 ) dt

Upvotes: 2

Related Questions