Limit CONCAT to 32.767 chars [IMPALA]-CodePudding

Basically, I have a table that looks something like this:

| id | string_col |
| -- | ---------- |
| 1  | aaaaaaa    |
| 1  | bbbbbbb    |
| 1  | ccccccc    |
| 2  | aaaaaaa    |
| 2  | bbbbbbb    |
| 2  | ccccccc    |

and a query that groups the rows by ids and concatenates the string_col values for that id:

SELECT
    id,
    CONCAT('[', GROUP_CONCAT(DISTINCT(string_col)), ']') AS strings_list
FROM my_table
GROUP BY id

the result looks like this:

| id | strings_list                |
| -- | --------------------------- |
| 1  | [aaaaaaa, bbbbbbb, ccccccc] |
| 2  | [aaaaaaa, bbbbbbb, ccccccc] |

The query does what I want, but I need to export the results in excel. The cell max length in excel is 32.767 characters and in some cases the length for strings_list is way above that and messes up the entire file.

Is there any way I could limit the CONCAT to 32.767 chars?

CodePudding user response：

Just use the LEFT function e.g.

LEFT(CONCAT('[', GROUP_CONCAT(DISTINCT(string_col)), ']'))