Search code examples
sqlprestotrino

Convert array(double) to varchar in Presto

I'm trying to convert Array(double) to varchar in Presto. A sample value: [99.0,98.0,99.0,95.0,99.0,88.0,90.0,79.0,90.0,56.0,90.0,90.0,92.0,90.0,93.0,99.0]

I tried the cast function below:

cast(colname as varchar)

But got this error message: "Cannot cast array(double) to varchar"

Any ideas how to convert this array to varchar. Thanks

Solution

  • You can use array_join:

    array_join(x, delimiter, null_replacement) → varchar

    Concatenates the elements of the given array using the delimiter and an optional string to replace nulls.

    SELECT array_join(ARRAY [1, 2],  ', ') -- 1, 2

    Or cast to json and use json_format:

    SELECT json_format(cast(ARRAY [1, 2] as json)) -- [1,2]