doc-exports/docs/dli/sqlreference/dli_08_0356.html

string_split

The string_split function splits a target string into substrings based on the specified separator and returns a substring list.

Description

string_split(target, separator)

Table 1 string_split parameters

Parameter	Type	Description
target	STRING	Target string to be processed NOTE: If target is NULL, an empty line is returned. If target contains two or more consecutive separators, an empty substring is returned. If target does not contain a specified separator, the original string passed to target is returned.
separator	VARCHAR	Delimiter. Currently, only single-character delimiters are supported.

Example

1. Prepare test input data.

   Table 2 Source table disSource

   target (STRING)	separator (VARCHAR)
   test-flink	-
   flink	-
   one-two-ww-three	-

2. Write test SQL statements.

   create table disSource(
     target STRING,
     separator  VARCHAR
   ) with (
     "connector.type" = "dis",
     "connector.region" = "xxx",
     "connector.channel" = "ygj-dis-in",
     "format.type" = 'csv'
   );
   
   create table disSink(
     target STRING,
     item STRING
   ) with (
     'connector.type' = 'dis',
     'connector.region' = 'xxx',
     'connector.channel' = 'ygj-dis-out',
     'format.type' = 'csv'
   );
   
   insert into
     disSink
   select
     target,
     item
   from
     disSource,
   lateral table(string_split(target, separator)) as T(item);

3. Check test results.

   Table 3 disSink result table

   target (STRING)	item (STRING)
   test-flink	test
   test-flink	flink
   flink	flink
   one-two-ww-three	one
   one-two-ww-three	two
   one-two-ww-three	ww
   one-two-ww-three	three

Parent topic: Table-Valued Functions