0

Here's my array:

set @fruit = 'apples,oranges,passion fruit,bananas,pineapples';

How do I output those into separate rows, i.e.:

apples
oranges
passion fruit
bananas
pineapples

Thanks.

huey
  • 115
  • 9
  • use `SUBSTRING_INDEX` to find each word, then use `print` to ouput them – Felix Jun 27 '17 at 01:50
  • https://stackoverflow.com/questions/14950466/how-to-split-the-name-string-in-mysql check here – Felix Jun 27 '17 at 01:51
  • Try this link: [stored procedure explode/split_string mysql](https://stackoverflow.com/questions/5928599/equivalent-of-explode-to-work-with-strings-in-mysql) – L42 Jun 27 '17 at 01:51

1 Answers1

0

As in my answer to this question, an alternative to create a function and/or temporary table:

SELECT @i
     , substring(@string, @start, @end-@start) str
   FROM <BigTable>
     , ( SELECT @string := 'apples,oranges,passion fruit,bananas,pineapples'
              , @start := 0
              , @end := 0
              , @i := 0
              , @len := length(@string)
              , @n := @len-length(replace(@string,',',''))+1
       ) t
   WHERE (@i := @i+1) <= @n
     AND (@start := @end+1)
     AND (@end := if((@loc:=locate(',',@string,@start))=0,@len+1,@loc))
Sal
  • 1,307
  • 1
  • 8
  • 16
  • Wonderful, thanks. I needed to add `+1` to the end of `@len := length(@string)` for the "s" in pineapples to be printed :-), Thanks again – huey Jul 18 '17 at 02:59