Using TEXTTABLE to Implement SPLIT / SPLIT_PART

Can I split large strings into columns based on a delimiter?

This can be done using the TEXTTABLE construct which has to be placed within a FROM clause.

Show me how

Working with a fixed string

SELECT a.*
FROM TEXTTABLE( 'hello,5,world,2018-01-10,3.1415'
               COLUMNS
               col1 string,
               col2 integer,
               col3 string,
               col4 date,
               col5 double
               DELIMITER ',') as a;;

Working with a column from a table

SELECT a.*, b.*
FROM "ds_pg.salesorderdetail" as a,
         TEXTTABLE( rowguid
               COLUMNS
               col1 string,
               col2 string,
               col3 string,
               col4 string,
               col5 string
               DELIMITER '-') as b;;

Nesting TEXTTABLE to split an already split string again

SELECT y.colSub1, z.colSub2
FROM TEXTTABLE('valueA:val1:val2+23:valA:valB+2018-01-05'
               COLUMNS
               col1 string,
               col2 string,
               emptyColumn string
               DELIMITER '+') x,
         TEXTTABLE(x.col1
              COLUMNS
              colSub1 string
              DELIMITER ':') as y,
        TEXTTABLE(x.col2
              COLUMNS
              colSub2 string
              DELIMITER ':') as z;;

Overall conversion from SPLIT_PART (based on the PG implementation)

SELECT ...SPLIT_PART(<string>, <delimiter>, 1) as <col1>, 
               SPLIT_PART(<string>, <delimiter>, 2) as <col2>, 
               SPLIT_PART(<string>, <delimiter>, 3) as <col3> 

can be translated to

SELECT ..., <alias>.<col1>
FROM ...
         TEXTTABLE( <string>
               COLUMNS
               <col1> <datatype>,
               <col2> <datatype>,
               <col3> <datatype>
               DELIMITER <delimiter>) as <alias>;;

Note that you define all columns withing the COLUMNS section instead of having to write multiple TEXTTABLE statements, as you would have for SPLIT_PART.