Array output functions are used to specify the output format of array data. For example, you can output each element of an array as a string and separate the elements with commas. OceanBase Database currently supports the array_to_string() function.
array_to_string
The array_to_string() function converts an array to a string. Specifically, it prints all base elements of the array as a string based on the specified delimiter and null element symbol. If the array is a nested array, it recursively prints its non-empty subarrays. It is not completely the inverse function of string_to_array(). Syntax:
array_to_string(arr1, delimiter[, null_str])
The input parameters are described as follows:
arr1: The input type must be an array.delimiter: The delimiter, which must be a character type, such as CHAR or VARCHAR.null_str(optional): The null element symbol, which must be a character type, such as CHAR or VARCHAR. If you do not specify this parameter, the function does not print NULL elements and their corresponding delimiters.
The return value is a string of the TEXT type.
Here are some examples:
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