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.
