Custom sql trino

Question & Answer
,
1

Hello, how do I disaggregate a field using custom SQL through Athena (Trino 438)?
Starting from a table like this

col 1 topic
a cloud, security
b security, application
c application

I need to have an output table like this:

col 1 topic
a cloud
a security
b security
b application
c application
2

Hi @andreab
not sure if it is the easiest/shortest way but:

WITH RECURSIVE SplitStrings AS (
    SELECT
        col_1,
        SUBSTRING(topic, 1, CASE WHEN CHARINDEX(',', topic) = 0 THEN LENGTH(topic) ELSE CHARINDEX(',', topic) - 1 END) AS SplitValue,
        SUBSTRING(topic, CHARINDEX(',', topic) + 1, LENGTH(topic)) AS RemainingString
    FROM
        table1
    WHERE
        CHARINDEX(',', topic) > 0
    
    UNION ALL
    
    SELECT
        col_1,
        SUBSTRING(RemainingString, 1, CASE WHEN CHARINDEX(',', RemainingString) = 0 THEN LENGTH(RemainingString) ELSE CHARINDEX(',', RemainingString) - 1 END),
        SUBSTRING(RemainingString, CHARINDEX(',', RemainingString) + 1, LENGTH(RemainingString))
    FROM
        SplitStrings
    WHERE
        CHARINDEX(',', RemainingString) > 0
)
SELECT
    col_1,
    SplitValue
FROM
    SplitStrings
UNION ALL
SELECT
    col_1,
    RemainingString
FROM
    SplitStrings
WHERE
    CHARINDEX(',', RemainingString) = 0;

BR

3

Hi @ErikG

I’ve tried as follows (the columns are actually “url” and “tags”). But it returns this error “Amazon Invalid operation: Recursive CTE must not omit column names.”

WITH RECURSIVE SplitStrings AS (
    SELECT
        url,
        SUBSTRING(tags, 1, CASE WHEN CHARINDEX(',', tags) = 0 THEN LENGTH(tags) ELSE CHARINDEX(',', tags) - 1 END) AS SplitValue,
        SUBSTRING(tags, CHARINDEX(',', tags) + 1, LENGTH(tags)) AS RemainingString
    FROM
        reparticoli.posts
    WHERE
        CHARINDEX(',', tags) > 0
    
    UNION ALL
    
    SELECT
        url,
        SUBSTRING(RemainingString, 1, CASE WHEN CHARINDEX(',', RemainingString) = 0 THEN LENGTH(RemainingString) ELSE CHARINDEX(',', RemainingString) - 1 END),
        SUBSTRING(RemainingString, CHARINDEX(',', RemainingString) + 1, LENGTH(RemainingString))
    FROM
        SplitStrings
    WHERE
        CHARINDEX(',', RemainingString) > 0
)
SELECT
    url,
    SplitValue
FROM
    SplitStrings
UNION ALL
SELECT
    url,
    RemainingString
FROM
    SplitStrings
WHERE
    CHARINDEX(',', RemainingString) = 0;
```Preformatted text
4

Hi @andreab
could you solve the issue.
Maybe you have to adjust the syntax to Athena.
BR