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.colSub2FROM 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.