CODEWITHSUNDEEP
sqlexasol
Vega
Vega

Reputation: 2929

How to split a string into a single column with multiple rows?

I have a column named fruits:

CREATE TABLE fruits (fruits varchar(50));
INSERT INTO analytics.fruits
values
    ('apple')
    ('banana')
    ('lemon')
    ('kiwi')
;
fruits
apple, banana, lemon, kiwi

I want to split that column into multiple rows, one row per element in the string:

fruits
apple
banana
lemon
kiwi

How can I do that in Exasol?

Upvotes: 0

Views: 75

Answers (1)

jnisen
jnisen

Reputation: 64

I haven't used Exasol but maybe with this code that works you can figure out, how to work with Exasol

WITH RECURSIVE num AS
  (SELECT 1 AS n )

SELECT 
 concat(ucase(substring(trim(substring_index(substring_index(name, ",", n), ",", -1)), 1, 1)), 
 lcase(substring(trim(substring_index(substring_index(name, ",", n), ",", -1)), 2))) AS Fruits
FROM fruits f
JOIN num ON n <= length(name) - length(replace(name, ",", "")) + 1
ORDER BY 1 DESC;

This code is from MySQL

Upvotes: 0

Related Questions