How to split values in Several rows (MySQL)

Question

I want split one row in several rows in SQL.

For example, I have 'A ADCT DROP INCALL -' that contain spaces and a '-' and I want that there values remain separate in several rows for example.

Original result:

SELECT statuses FROM campaigns;
+-----------------------------------------+
| RESULT OF QUERY                         |
+-----------------------------------------+
| A ADCT DROP INCALL -                    |
+-----------------------------------------+

The result must like:

+-----------------------------------------+
| RESULT OF QUERY                         |
+-----------------------------------------+
| A                                       |
| ADCT                                    |
| DROP                                    |
| INCALL                                  |
+-----------------------------------------+

I try with SUBSTRING_INDEX like:

SELECT status
FROM statuses
WHERE statuses.status NOT IN (SELECT SUBSTRING_INDEX(TRIM(dial_statuses)," ", 1) FROM campaigns WHERE campaign_id = '4000')
ORDER BY STATUS;

I'm sorry, I know that is so basic.

score 0 · Answer 1 · answered Aug 29 '17 at 13:40

You could try and create function in MySQL as follows:

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
   LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
   delim, '');

Then you should be able to use it like this in a query:

SELECT SPLIT_STR('a|bb|ccc|dd', '|', 3) as third;

This is untested but should work. For more info check this out.

score 0 · Answer 2 · answered Aug 29 '17 at 13:46

If i'm reading this correctly, then this question has already been asked here:

SQL split values to multiple rows

The answer states the following from @fthiella:

select
tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
  (select 1 n union all
   select 2 union all select 3 union all
   select 4 union all select 5) numbers INNER JOIN tablename
  on CHAR_LENGTH(tablename.name)
     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
  id, n

See a working example here:

http://sqlfiddle.com/#!9/ffa86a/1

score 0 · Accepted Answer · answered Aug 29 '17 at 13:50

I believe you can do it like this:

SELECT
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) 
  name
FROM
  numbers 
INNER JOIN tablename ON CHAR_LENGTH(tablename.name)
 -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
ORDER BY
  id, n

Please see fiddle here.

If you cannot create a table, then a solution can be this:

SELECT
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) 
  name
FROM
  (select 1 n union all
   select 2 union all select 3 union all
   select 4 union all select 5) numbers 
INNER JOIN tablename ON CHAR_LENGTH(tablename.name)
 -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
ORDER BY
  id, n

an example fiddle is here.

score 0 · Answer 4 · answered Aug 29 '17 at 13:59

0

If you have to replace only spaces and - then you can use

SELECT TRIM(BOTH "\n" FROM REPLACE(REPLACE(column_name,' ', "\n"), "-", "\n")) FROM table;

you can skip trim from above statement if you want leading or trailing spaces from start and end.

answered Aug 29 '17 at 13:59

pawankumar meshram

55
4

How to split values in Several rows (MySQL)

4 Answers4