Teradata Split Delimited Fields into Table Records and Examples

  • Post author:
  • Post last modified:February 26, 2018
  • Post category:General
  • Reading time:3 mins read

If you are working on the huge amount of different source system then you may come across the requirement of Teradata split delimited fields into table records. You can perform Teradata split delimited string into columns in various ways using Teradata built-in string functions or Teradata regular expressions.

You can use any of the below methods as per your requirements:

Teradata Split Delimited fields using STRTOK_SPLIT_TO_TABLE Function 

Since TD14, there is a STRTOK_SPLIT_TO_TABLE function. You can use this function to split your string or delimited field into table columns.

Teradata Split Delimited fields using STRTOK_SPLIT_TO_TABLE Example

Below is the example on usage of the function:

SELECT * 
FROM TABLE (STRTOK_SPLIT_TO_TABLE('string1', 'This,is,to,test,split,string', ',')
 RETURNS (outkey VARCHAR(10) CHARACTER SET UNICODE
 ,tokennum INTEGER
 ,token VARCHAR(30) CHARACTER SET UNICODE)
 ) AS dt

outkey tokennum token
string1 1 This
string1 2 is
string1 3 to
string1 4 test
string1 5 split
string1 6 string

Teradata Split Delimited fields using regexp_split_to_table Function

You can use Teradata regular expression regexp_split_to_table to split delimited row into multiple columns based on the delimiter value.

You can read about Regular expression:

Teradata Split Delimited fields using regexp_split_to_table Function Example

Below is the example on usage of the regular expression function:

SELECT * 
FROM TABLE (regexp_split_to_table('string1', 'This,is,to,test,split,string', ',','c')
 RETURNS (outkey VARCHAR(30) CHARACTER SET UNICODE
 ,tokennum INTEGER
 ,token VARCHAR(40) CHARACTER SET UNICODE)
 ) AS dt

outkey tokennum token
string1 1 This
string1 2 is
string1 3 to
string1 4 test
string1 5 split
string1 6 string

Teradata Split Delimited fields using STRTOK Function

This function tokenize the string based on the delimiter. Below is the example:

select STRTOK('This,is,to,test,split,string', ',',1);
This

select STRTOK('This,is,to,test,split,string', ',',2);
is

You have to explicitly specify the position of the token that you want to extract.

Read:

Tags: