Hi i have data in a mysql table which is about 70000 rows. In one column there is information that I would like to split into 2 rows. The information I want to send over to the other named row is nicely sat in brackets eg (18) . It is imperative whilst i move these over to the new named row that they stay aligned with the same row, as the information will be all wrong if not.

EG - i need to move the (18) and (24) over to colomn 4 and delete from column 2 the (18) and (24)

Colomn 1 Colomn 2 Colomn 3 Colomn 4

row 1 xxxxxx xxx (18) xxxxxxx

row 2 xxxxxx xxx (18) xxxxxxx

row 3 xxxxxx xxx (24) xxxxxxx

row 4 xxxxxx xxx (18) xxxxxxx


What code would I need to run to enable me to do this.

Thanks for your help

It is difficult to tell where one column ends and the next one starts from your posting. Next time may I suggest you use the CODE tag to force a non proportional font or do something like this to get a format and definition of the first 10 records we can easily ready and generate code for:

mysqldump -c -u dbuser -puserpassword MyDatabase MyTable -w"ID<10"

However if it is a fixed lenght file and the (18) and (24) are always in positions 4,5,6 and 7 of column2 then it is easy. Parts of strings can be extracted and displayed. For example, LEFT( ), MID( ), and RIGHT( ) extract substrings from the left, middle, or right part of a string.

I would create a query to select the results first, then post them either to a new table and when the new table is correct upload it in place of the old. Or use update to add two new columns (Column4 and Column5) and once I have verified the output write column 5 to column2 and delete 5.

Creating a new table has always worked best for me. That way I can run it over and over till I get the parsing correct and have the original table intact.

First get your select working
Just in case I am assuming there are some characters in Column2 after the (18) and (24) that you might need in the new Colum2. If not all you need is the left( ) and you can drop the concat and right.

SELECT Column1, 
CONCAT(left(column2,3), right(Column2,<number of characters after (18) and (24))),
Column3, 
mid(Column2,4,4) 
from mytable

Then write it to the new table:

Create Table NewTable SELECT Column1, 
CONCAT(left(column2,3), right(Column2,<number of characters after (18) and (24))),
Column3, 
mid(Column2,4,4) 
from mytable

If NewTable looks good relace mytable with it.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.