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.