MySQL string split

Question

I uploaded a csv file to MySQL DB but for some reason data from two columns got glued to one.

e.g. the data 0880174V,D should have gone to two different columns.

Is there a way (via sql) i can split the data from this column (i.e. as 0880174V and D) and update the table, without having to delete the record and re-upload? I have a million records.

==CURRENT

Column A       Column B
0880174V,D

== EXPECTED

Column A       Column B
0880174V        D

Jorge Ferreira · Accepted Answer · 2010-07-02 21:07:01Z

6

UPDATE my_table
SET ColumnB = SUBSTRING(ColumnA, INSTR(ColumnA, ',') + 1),
    ColumnA = SUBSTRING(ColumnA, 1, INSTR(ColumnA, ',') - 1)

Do a SELECT TOP first to ease your mind.

edited Jul 2, 2010 at 21:07

answered Jul 2, 2010 at 20:34

Jorge Ferreira

98.2k25 gold badges127 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kushal Paudyal Over a year ago

Thank you very much. Although it kind of did the reverse (D 0880174V) instead of (0880174V D), i got the insight. Appreciate your help.

Jorge Ferreira Over a year ago

Yap, you are right. Sorry, my bad. Changed the answer in any case. Is late in my time zone, if that is to any aval :)

Imre L · Accepted Answer · 2010-07-02 21:20:24Z

1

You have to specify field terminator

load data local infile 'file.csv' into myTable
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(col1,col2)

EDIT: nvm

edited Jul 2, 2010 at 21:20

answered Jul 2, 2010 at 21:14

Imre L

6,29126 silver badges32 bronze badges

Comments

w43L · Accepted Answer · 2011-04-05 12:50:15Z

1

a simpler solution is to use SUBSTRING_INDEX instead of SUBSTRING with INSTR

UPDATE my_table
SET ColumnB = SUBSTRING_INDEX(ColumnA, ',' , 1),
ColumnA = SUBSTRING_INDEX(ColumnA, ',' , - 1)

answered Apr 5, 2011 at 12:50

w43L

5651 gold badge8 silver badges26 bronze badges

Collectives™ on Stack Overflow

MySQL string split

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related