teradata筆記-strtok_split_to_table

1、建表&插數

CREATE TABLE tab (name varchar(5), str varchar(20));

insert into tab VALUES (‘A’,’1234;4568;4567;456');
insert into tab values (‘B’,’5432;;;;’);
insert into tab values (‘C’,’5432,,,,’);
insert into tab values (‘D’,’5432’);
           

2、分割成表

—備注 tokennum必須是integer

SELECT T.* 
FROM TABLE (
    strtok_split_to_table(tab.name, tab.str, ‘;,')
    RETURNS (name varchar(5), tokennum integer, token varchar(20) character set unicode) 
) as T; 
           

3、傳回

name tokennum【序号】 token
A 1 1234
A 2 4568
A 3 4567
A 4 456
B 1 5432
C 1 5432
D 1 5432