CODEWITHSUNDEEP
sqlmysqlstring
Kushal Paudyal
Kushal Paudyal

Reputation: 3811

MySQL string split

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

Upvotes: 1

Views: 4028

Answers (3)

w43L
w43L

Reputation: 565

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)

Upvotes: 1

Imre L
Imre L

Reputation: 6249

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

Upvotes: 1

Jorge Ferreira
Jorge Ferreira

Reputation: 97997

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.

Upvotes: 6

Related Questions