# Using TEXTTABLE to Implement SPLIT / SPLIT\_PART

**URL:** <https://community.cdata.com/t/using-texttable-to-implement-split-split-part/825>\
**Category:** CData Virtuality \[Archived\]\
**Tags:** article, cdata-virtuality\
**Created:** [July 12, 2024, 4:15pm UTC](https://community.cdata.com/t/using-texttable-to-implement-split-split-part/825 "2024-07-12T16:15:08Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![anonymous](https://avatars.discourse-cdn.com/v4/letter/a/8491ac/32.png) [@anonymous](https://community.cdata.com/u/anonymous)\
**Post date:** [July 12, 2024, 4:15pm UTC](https://community.cdata.com/t/using-texttable-to-implement-split-split-part/825/1 "2024-07-12T16:15:08Z")

</div>

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