Array output functions are used to select the output format for array data. For example, they can output each element of an array as a string in sequence, separated by commas. The only supported array output function in OceanBase AI Database is array_to_string().
array_to_string
The array_to_string() function converts an array into a string. Specifically, it prints all the primitive elements in the array as strings, using the specified separator and empty-element character. If the array is a nested array, its non-empty sub-arrays are printed recursively. It is not a complete inverse function of Array functions. The syntax is as follows:
array_to_string(arr1, delimiter[, null_str])
The input parameters are described as follows:
- The type of
arr1must be an array. delimiter: the delimiter, which can be a character type, including Char and Varchar.null_str(optional): The null character. Character types, including CHAR and VARCHAR, are supported. If not specified, NULL elements and their corresponding separators will not be printed.
The return value is a string of the TEXT type.
An example is as follows:
SELECT array_to_string([1,2,NULL,3], '-', 'N');
+-----------------------------------------+
| array_to_string([1,2,NULL,3], '-', 'N') |
+-----------------------------------------+
| 1-2-N-3 |
+-----------------------------------------+
1 row in set
SELECT array_to_string([1,2,NULL,3], 'and');
+--------------------------------------+
| array_to_string([1,2,NULL,3], 'and') |
+--------------------------------------+
| 1and2and3 |
+--------------------------------------+
1 row in set
SELECT array_to_string([["hello", "world"], ["hi", "what"]], '-');
+------------------------------------------------------------+
| array_to_string([["hello", "world"], ["hi", "what"]], '-') |
+------------------------------------------------------------+
| hello-world-hi-what |
+------------------------------------------------------------+
1 row in set
SELECT array_to_string([["hello", "world"], ["hi", "what"], NULL], '-', 'N');
+-----------------------------------------------------------------------+
| array_to_string([["hello", "world"], ["hi", "what"], NULL], '-', 'N') |
+-----------------------------------------------------------------------+
| hello-world-hi-what-N |
+-----------------------------------------------------------------------+
1 row in set