how to implement connect by level in teradata

Database

how to implement connect by level in teradata

Hi Guys

I am new to tera data and we are migrating from oracle to teradata. I have a query like 

SELECT regexp_substr('A,B,C', '[^,]+', 1, level) from dual connect by level <= regexp_count('A,B,C',

',') + 1

I have searched the forum and it looks like that tera data does'nt support dual and connect by level.How can i migrate it to teradata.

Any help will be appreciated!!

regards

Anirudh

2 REPLIES
Junior Contributor

Re: how to implement connect by level in teradata

Hi Anirudh,

Teradata support Standard SQL's WITH RECURSIVE instead of CONNECT BY.

But you don't need recursion to split a string, in TD14 there's STRTOK_SPLIT_TO_TABLE, e.g.

SELECT * 
FROM TABLE (STRTOK_SPLIT_TO_TABLE(1,'A,B,C',',')
RETURNS (outkey INTEGER
,tokennum INTEGER
,token VARCHAR(30) CHARACTER SET UNICODE)
) AS dt

Re: how to implement connect by level in teradata

Thanks Dieter. It was a great help .

regards

Anirudh