CODEWITHSUNDEEP
sqlpresto
Scala_Beginner
Scala_Beginner

Reputation: 131

Split one Row to Multiple Row Based on New line Character with Presto

I have a table in Presto:

Table 1

I want:

Table 2

I have used UNNEST function with split function but I am not able to split each line using '\n' or char(10)

SELECT Col1,split_Col2 
FROM tbl
CROSS JOIN UNNEST(SPLIT(Col2,'\n')) AS t (split_Col2)

does not work.

Even this:

SELECT Col1,split_Col2 
FROM tbl
CROSS JOIN UNNEST(SPLIT(Col2,char(10))) AS t (split_Col2)

does not work.

I am very new to Presto and it would be great if someone could help!

Upvotes: 3

Views: 5831

Answers (2)

Monish Kumar K
Monish Kumar K

Reputation: 11

you have to give the actual newline (enter button) instead of a escape sequence '\n' or char(10)

use: 

SELECT Col1,split_Col2  FROM tbl CROSS JOIN UNNEST(SPLIT(Col2,'  
')) AS t (split_Col2)

This works

Upvotes: 1

Ike Walker
Ike Walker

Reputation: 65547

I think you meant to use the chr() function:

SELECT Col1,split_Col2 
FROM tbl
CROSS JOIN UNNEST(SPLIT(Col2,chr(10))) AS t (split_Col2)

Upvotes: 1

Related Questions