string_to_array()

Purpose: Return a one-dimensional text[] array by splitting the input text value into subvalues using the specified text value as the "delimiter". Optionally, allows a specified text value to be interpreted as NULL.

Signature:

input value:       text, text [, text]
return value:      text[]

Example:

select string_to_array(
  'a|b|?|c', -- the to-be-split string
  '|',       -- the character(s) to be taken as the delimiter
  '?'        -- the character(s) to be taken to the null indicator
) as "string_to_array result";

It produces thus result:

 string_to_array result
------------------------
 {a,b,NULL,c}

Semantics:

The interpretation of the "delimiter" text value and the "null indicator" text uses this priority rule:

  • First, the "delimiter" text value is consumed

  • and only then is the "null indicator" text value consumed.

However, this rule matters only when these two critical values are defined by more than one character and when one starts with a sequence that the other ends with.

Yugabyte recommends, therefore, that when you can choose the text values for the "delimiter" and for the "null indicator", you choose two different single characters. This is what the example, above, does. Of course, you must be sure that neither occurs in any of the text values that you want to convert into text[] arrays. (There is no escaping mechanism.)

Predicting the outcome, when unfortunate choices for these two values were made, will require some mental effort. The DO block below demonstrates an example of this:

  • The "delimiter" is ' !'::text.

  • The "null indicator" is '~ '::text.

And the input contains this sequence:

    <tilda><space><exclamationPoint>

The troublesome sequence is shown in typewriter font here:

    dog house !~ !x! ~ ! cat flap ! !

These considerations, together with the fact that it can produce only a text[] output, mean that the string_to_array() function has limited usefulness.

do $body$
declare
  delim_text  constant text := ' !';
  null_text   constant text := '~ ';

  input_text  constant text := 'dog house !~  !x! ~ ! cat flap !  !';

  result constant text[] :=
    string_to_array(input_text, delim_text, null_text);

  good_delim_text constant text := '|';
  good_null_text  constant text := '?';

  delim_first_text constant text :=
    replace(replace(
      input_text,
      delim_text, good_delim_text),
      null_text,  good_null_text);

  null_first_text constant text :=
    replace(replace(
      input_text,
      null_text,  good_null_text),
      delim_text, good_delim_text);

  delim_first_result constant text[] :=
    string_to_array(delim_first_text, good_delim_text, good_null_text);

  null_first_result constant text[] :=
    string_to_array(null_first_text, good_delim_text, good_null_text);

  -- Notice that one of the special characters, "!", remains in
  -- both expected_result and unexpected_result.
  -- If
  expected_result constant text[] :=
    '{"dog house",NULL,"x! ~"," cat flap"," ",""}';
  unexpected_result constant text[] :=
    '{"dog house",NULL,"x! ?! cat flap"," ",""}';

begin
  assert
  (result             =  expected_result)    and
  (delim_first_result =  expected_result)    and
  (null_first_result  <> delim_first_result) and
  (null_first_result  =  unexpected_result)  and
    true,
  'unexpected';
end;
$body$;