CODEWITHSUNDEEP
mysqlstring
Deepa Aranha
Deepa Aranha

Reputation: 31

Split string in Mysql by colon and update a table record

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

Upvotes: 2

Views: 2943

Answers (1)

Zahid Ali
Zahid Ali

Reputation: 466

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)

Upvotes: 4

Related Questions