1

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

Improve this question
2
  • How many rows in the table ? Commented Oct 22, 2014 at 3:39
  • a lot. More than 30K+ Commented Oct 22, 2014 at 3:40

2 Answers 2

Reset to default
1 
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,',','')
;
Improve this answer
Sign up to request clarification or add additional context in comments.

14 Comments

sql on your first one gives error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'match SET username=LEFT(load,LOCATE(',',load)), load=SUBSTR(load,LOCAT' at line 1
second idea gives error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'match SET username=LEFT(load,LOCATE(',',load))' at line 1
MATCH is a function for FULLTEXT operations (dev.mysql.com/doc/refman/5.5/en/…). I added backquotes around the word match. Please try again.
still no luck. both ideas returning errors #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'load,LOCATE(',',load)), load=SUBSTR(load,LOCATE(',',load)+1)' at line 2
Ugh LOAD is a reserved word too.
|
1

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);
Improve this answer

3 Comments

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'match set username=strSplit(load, ',', 1),username_clean=strSplit(load, ',', 2),' at line 1
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'match set username=strSplit(load, ',', 1),username_clean=strSplit(load, ',', 2),' at line 1
you are using sql or mysql?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.