What is the equivalent of "connect by" in Teradata

Database

What is the equivalent of "connect by" in Teradata

Example: text = 'V_TASK_1' or text ='V_TASK_1_2 or text = 'V_TASK_1_2_3 ... etc.

Need to split a string by string.

It can be done in Oracle. 

select substr(str, b, l) result

   from (

       select str

                        , delimiter

                        , i

                        , b

                        , case instr(str, delimiter, b) when 0 then b

                                    else instr(str, delimiter, b)-b

                          end l

   from (

       select str

                                      , delimiter

                                      , level i

                                      , case level when 1 then 1

                                                  else instr(str, delimiter, 1, level-1)+1

                                        end b

          from (

                                          select 'V_TASK_1_2' str

                                                    , '_' delimiter

                                             from dual

                                         )

                      connect by instr(str, delimiter, 1, level-1) != 0

                            )

             )

 

Teradata does not have syntax "connection by"...

How can I resolve this problem using the syntax Teradaty?

Tell me, please.

2 REPLIES

Re: What is the equivalent of "connect by" in Teradata

Tried to implement it using "WITH RECURSIVE":

WITH RECURSIVE sub (str,delimeter,lvl,pos) AS

    (

    SELECT A.STR,A.DELIMITER,A.LVL,INDEX(A.STR, A.DELIMITER) AS pos

           FROM (SELECT 'V_ART_PRICE_LINE_LNK' AS STR, '_' AS DELIMITER, 0 LVL ) A

    UNION ALL

    SELECT SUBSTR(str,pos+1,LENGTH(str)) str_out

        , delimeter

        , lvl+1 AS lvl

        , INDEX(str_out,delimeter) pos

      FROM sub

     WHERE pos>0

    )

SELECT * FROM sub

I have result:

V_ART_PRICE_LINE_LNK _ 0 2
ART_PRICE_LINE_LNK _ 1 4
PRICE_LINE_LNK _ 2 6
LINE_LNK _ 3 5
LNK _ 4 0

Re: What is the equivalent of "connect by" in Teradata

I found the solution !!! :-)

Example:

WITH RECURSIVE SUB (STR,DELIMETER,LVL,LEN,POS) AS

(

SELECT A.STR,A.DELIMITER,A.LVL, CAST(LENGTH(A.STR) AS INTEGER) LEN,INDEX(A.STR, A.DELIMITER) AS POS

        FROM (SELECT 'V_ART_PRICE_LINE_LNK' AS STR, '_' AS DELIMITER, 0 LVL ) A

UNION ALL

SELECT SUBSTR(STR,POS+1,LENGTH(STR)) STR_OUT

  , DELIMETER

  , LVL+1 AS LVL

  , CAST(LENGTH(STR_OUT) AS INTEGER) AS LEN

  , INDEX(STR_OUT,DELIMETER) POS

   FROM SUB

  WHERE POS>0

)

SELECT CASE WHEN POS>0 THEN SUBSTR(STR,1,POS-1)

        ELSE STR

    END

  FROM SUB