CODEWITHSUNDEEP
mysqlcsvsplit
Johnson Jason
Johnson Jason

Reputation: 721

splitting mysql col with csv values into respective fields

I have a mysql table called "match"

there are 5 fields in match (4 blank col's and one col with CSV data) load,username,username_clean,user_id,member_id

In the load field are many rows with comma separated values (CSV) values in load column are in this format username,username_clean,user_id,member_id that's 4 csv values in the first column "load"

QUESTION: How can I split the 4 values in load and place them in their respective columns?

Please help! Thank you in advance. example. I want the following

|Sara,sara,60,9|

to become: Sara| sara| 60| 9| <--4cols instead of 1

Upvotes: 1

Views: 116

Answers (2)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44353

UPDATE `match` SET
    username=LEFT(`load`,LOCATE(',',`load`)-1),
    `load`=SUBSTR(`load`,LOCATE(',',`load`)+1)
;
UPDATE `match` SET
    username_clean=LEFT(`load`,LOCATE(',',`load`)-1),
    `load`=SUBSTR(`load`,LOCATE(',',`load`)+1)
;
UPDATE `match` SET
    user_id=LEFT(`load`,LOCATE(',',`load`)-1),
    member_id=SUBSTR(`load`,LOCATE(',',`load`)+1)
;

or

UPDATE `match` SET username=LEFT(`load`,LOCATE(',',`load`)-1);
UPDATE `match` SET `load`=SUBSTR(`load`,LOCATE(',',`load`)+1);
UPDATE `match` SET username_clean=LEFT(`load`,LOCATE(',',`load`)-1);
UPDATE `match` SET `load`=SUBSTR(`load`,LOCATE(',',`load`)+1);
UPDATE `match` SET user_id=LEFT(`load`,LOCATE(',',`load`)-1);
UPDATE `match` SET member_id=SUBSTR(`load`,LOCATE(',',`load`)+1);

Since my first attempt added a comma, just run this

UPDATE matchindex SET
    username = REPLACE(username,',',''),
    username_clean = REPLACE(username_clean,',','')
;

Upvotes: 1

Karunakar
Karunakar

Reputation: 2349

Try This:
define a function strSplit:

CREATE FUNCTION strSplit(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, '');

use this query:

 update match set username=strSplit(load, ',', 1),username_clean=strSplit(load, ',', 2),user_id=strSplit(load, ',', 3),member_id=strSplit(load, ',', 4);

Upvotes: 1

Related Questions