Array output functions are used to specify the output format of array data, such as outputting each element of array data in the form of a string, separated by commas. OceanBase Database supports the array_to_string() function.
array_to_string
The array_to_string() function converts an array to a string. Specifically, it prints all the base elements in the array as strings based on the specified delimiter and null element string. If the array contains nested arrays, it recursively prints the non-empty subarrays. It is not completely the inverse function of string_to_array(). The syntax is as follows:
array_to_string(arr1, delimiter[, null_str])
The parameters are described as follows:
arr1must be of an array type.delimiterspecifies the delimiter. It must be of the Char or Varchar type.null_str(optional) specifies the null element string. It must be of the Char or Varchar type. If this parameter is not specified, NULL elements and their corresponding delimiters are not printed.
The return result 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