Athena- extract substring from string - comma delimited

Question

I want to create Athena view from Athena table.

In table, the column value is "lastname, firstname" so I want to extract these values as 'lastname' and 'firstname' and then need to store it into seperate columns in a view. example- firstname need to be stored into new column- 'first_name' and lastname need to be store into new column - 'last_name'

whats the SQL function which I can use here? I tried split function but then it's giving me an array.

Answer 1

Assuming the input strings have a fixed and known number of elements, you can do something like this:

WITH data(value) AS (
    VALUES ('Aaa,Bbb')
)
SELECT elements[1], elements[2]
FROM (
    SELECT split(value, ',') AS elements
    FROM data
)

=>

 _col0 | _col1
-------+-------
 Aaa   | Bbb
(1 row)