Teradata SQL Split Single String into Table Rows

Question

I have one string element, for example :

"(1111, Tem1), (0000, Tem2)"

and hope to generate a data table such as

var1	var2
1111	Tem1
0000	Tem2

This is my code, I created the lag token and filter with odd rows element.


with var_ as (
    select '(1111, Tem1), (0000, Tem2)' as pattern_
)
select tbb1.*, tbb2.result_string as result_string_previous
from(
    select tb1.*,
        min(token) over(partition by 1 order by token asc rows between 1 preceding and 1 preceding) as min_token
    from
        table (
            strtok_split_to_table(1, var_.pattern_, '(), ')
            returns (outkey INTEGER, token INTEGER, result_string varchar(20))
        ) as tb1) tbb1

inner join (select min_token, result_string from tbb1) tbb2
    on tbb1.token = tbb2.min_token

where (token mod 2) = 0;

But it seems that i can't generate new variables in "from" step and applied it directly in "join" step. so I wanna ask is still possible to get the result what i want in my procedure? or is there any suggestion?

Thanks for all your assistance.

Fred · Answer 1 · 2021-04-26T21:03:00.457

I wouldn't split / recombine the groups. Split each group to a row, then split the values within the row, e.g.

with var_ as (
    select '(1111, Tem1), (0000, Tem2)' as pattern_
),
split1 as (
    select trim(leading '(' from result_string) as string_  
    from
        table ( /* split at & remove right parenthesis */
            regexp_split_to_table(1, var_.pattern_, '\)((, )|$)','c')
            returns (outkey INTEGER, token_nbr INTEGER, result_string varchar(256))
        ) as tb1
)
select *
    from table(
            csvld(split1.string_, ',', '"')
            returns (var1 VARCHAR(16), var2 VARCHAR(16))
        ) as tb2
;

Teradata SQL Split Single String into Table Rows

1 Answers1