I have values (abc,123,aed,ghi) and i want to split values individual in my sql

Question

I have values [abc,123,aed,ghi] and i want to split values individual in mysql. result should be like this

column1 column2 column3 column4
  abc    123    aed      ghi

Answer 1

Relational database and non-atomic data is poor design . But you can use:

SELECT ID,
  MAX(CASE WHEN N = 1 THEN sub.val END) AS Column1,
  MAX(CASE WHEN N = 2 THEN sub.val END) AS Column2,
  MAX(CASE WHEN N = 3 THEN sub.val END) AS Column3,
  MAX(CASE WHEN N = 4 THEN sub.val END) AS Column4
FROM
(
  SELECT ID, SUBSTRING_INDEX(SUBSTRING_INDEX(t.HashTag, ',', n.n), ',', -1) AS val, N
  FROM (SELECT ID, Substring(my_csv_column, 2, LENGTH(my_csv_column) - 2) AS HashTag FROM my_non_normalized_table) AS t 
  CROSS JOIN 
  (
   SELECT a.N + b.N * 10 + 1 n
     FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
  ) n
   WHERE n.n <= 1 + (LENGTH(t.HashTag) - LENGTH(REPLACE(t.HashTag, ',', '')))
) sub
GROUP BY ID;

SqlFiddleDemo

Output:

╔═════╦══════════╦══════════╦══════════╦═════════╗
║ ID  ║ Column1  ║ Column2  ║ Column3  ║ Column4 ║
╠═════╬══════════╬══════════╬══════════╬═════════╣
║  1  ║ abc      ║ 123      ║ aed      ║ ghi     ║
║  2  ║ efg      ║ cde      ║ (null)   ║ (null)  ║
╚═════╩══════════╩══════════╩══════════╩═════════╝

You should read about data normalization and 1NF.

Answer 2

Unfortunately MySQL does not feature a split string function. However you can create a user defined function for this, such as the one described in the following article:

• MySQL Split String Function by Federico Cargnelutti

With that function, you would be able to build your query as follows:

 SELECT SPLIT_STR(fieldName, ',', 1) as abc,
      SPLIT_STR(fieldName, ',', 2) as 123,
      SPLIT_STR(fieldName, ',', 3) as aed,
      SPLIT_STR(fieldName, ',', 4) as ghi,
 FROM   YourTable;

Read This