STRING_TO_ARRAY

Splits a string containing array values and returns a native one-dimensional array. The output does not include the "ARRAY" keyword. This function does not support nested (multi-dimensional) arrays.

This function returns array elements as strings by default. You can cast to other types, as in the following example:

=> SELECT STRING_TO_ARRAY('[1,2,3]')::ARRAY[INT];

Behavior

Immutable

Syntax

STRING_TO_ARRAY(string [USING PARAMETERS param=value[,...]])

The following syntax is deprecated:

STRING_TO_ARRAY(string,delimiter)

Arguments

string

String representation of a one-dimensional array; can be a VARCHAR column, a literal string, or the string output of an expression.

Spaces in the string are removed unless elements are individually quoted. For example, ' a,b,c' is equivalent to 'a,b,c'. To preserve the space, use '" a","b","c"'.

Parameters

These parameters behave the same way as the corresponding options when loading delimited data (see DELIMITED (Parser)).

No parameter may have the same value as any other parameter.

collection_delimiter

The character or character sequence used to separate array elements (VARCHAR(8)). You can use any ASCII values in the range E'\000' to E'\177', inclusive.

Default: Comma (',').

collection_open, collection_close

The characters that mark the beginning and end of the array (VARCHAR(8)). It is an error to use these characters elsewhere within the list of elements without escaping them. These characters can be omitted from the input string.

Default: Square brackets ('[' and ']').

collection_null_element

The string representing a null element value (VARCHAR(65000)). You can specify a null value using any ASCII values in the range E'\001' to E'\177' inclusive (any ASCII value except NULL: E'\000').

Default: 'null'
collection_enclose

An optional quote character within which to enclose individual elements, allowing delimiter characters to be embedded in string values. You can choose any ASCII value in the range E'\001' to E'\177' inclusive (any ASCII character except NULL: E'\000'). Elements do not need to be enclosed by this value.

Default: double quote ('"')

Supported Data Types

  • VARCHAR

Examples

The function uses comma as the default delimiter. You can specify a different value:

=> SELECT STRING_TO_ARRAY('[1,3,5]');
 STRING_TO_ARRAY 
-----------------
 ["1","3","5"]
(1 row)

=> SELECT STRING_TO_ARRAY('[t|t|f|t]' USING PARAMETERS collection_delimiter = '|');
  STRING_TO_ARRAY  
-------------------
 ["t","t","f","t"]
(1 row)

The bounding brackets are optional:

=> SELECT STRING_TO_ARRAY('t|t|f|t' USING PARAMETERS collection_delimiter = '|');
  STRING_TO_ARRAY  
-------------------
 ["t","t","f","t"]
(1 row)

The input can use other characters for open and close:

=> SELECT STRING_TO_ARRAY('{NASA-1683,NASA-7867,SPX-76}' USING PARAMETERS collection_open = '{', collection_close = '}');
          STRING_TO_ARRAY           
------------------------------------
 ["NASA-1683","NASA-7867","SPX-76"]
(1 row)

By default the string 'null' in input is treated as a null value:

=> SELECT STRING_TO_ARRAY('{"us-1672",null,"darpa-1963"}' USING PARAMETERS collection_open = '{', collection_close = '}');
        STRING_TO_ARRAY        
-------------------------------
 ["us-1672",null,"darpa-1963"]
(1 row)

In the following example, the input comes from a column.

=> SELECT STRING_TO_ARRAY(name USING PARAMETERS collection_delimiter=' ') FROM employees;
    STRING_TO_ARRAY
-----------------------
 ["Howard","Wolowitz"]
 ["Sheldon","Cooper"]
(2 rows)