CODEWITHSUNDEEP
teradata-sql-assistant
hsieh weihsuan
hsieh weihsuan

Reputation: 21

Teradata SQL Split Single String into Table Rows

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.

Upvotes: 2

Views: 619

Answers (1)

Fred
Fred

Reputation: 2055

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
;

Upvotes: 1

Related Questions