Split string in Mysql by colon and update a table record

Question

I have created a table (Table A), which has a column (Column A) which stores values like this Example: ASUNMI:GI:PI:INP:EDM:20141001:NO34W:DERERTBYDAY14:NSW ASUNMI:GI:PI:HME:EDM:20140929:EO23M:WIERTNACAR:VICETC

I need to split this string and place the data in different columns.

Example:

   Column2=ASUNMI
   Column3=GI
   Column4=PI
   Column5=INP
   Column6=EDM

I need to split the above string based on colons(:).the no of colons in each field could differ hence I cannot use the

substring_index(çolumn,':',-2) property

I need to then use this to update a table

http://stackoverflow.com/questions/1673238/how-to-split-a-single-row-in-to-multiple-columns-in-mysql its been already answered — Zahid Ali, Jan 09 '15 at 05:02

Zahid Ali · Accepted Answer · 2015-01-09T05:30:15.130

4

this is a good link please check this out

if you want to update TableA you can write

  UPDATE `TableA` SET  `columName` = (SELECT SPLIT_STR(columnName, ':',1) as ColumnName from tableName)

or

UPDATE `TableA` SET  `columName` = (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(columName,':',1),':',-1) AS columName FROM tableName)

edited Jan 09 '15 at 05:30

answered Jan 09 '15 at 04:58

Zahid Ali

456
6
20

I am trying to use this to update a table. I am using this query but throws up an error. update table tableA set columnname = SPLIT_STR(column) – Deepa Aranha Jan 09 '15 at 05:10
@Programmer Please provide actual details in your answer, rather than just links to other websites. – Joel Cox Jan 09 '15 at 05:13

Split string in Mysql by colon and update a table record

1 Answers1