dividing field contents in Mysql

[Shalini Bhalla]

I have a table in which i have uploaded data full address of individuals including area and city , but now i have realized that i should have separate area and city columns . Is there any way by which i can divide my address in address1 , address 2 and city .

for ex

address1

flat no 1 , block b , ashok nagar , delhi

i want

address1...... address2..... city
flat no1......ashok nagar.....delhi
block b

[debasisdas]

You need to do so manually .

[mwasif]

I have a table in which i have uploaded data full address of individuals including area and city , but now i have realized that i should have separate area and city columns . Is there any way by which i can divide my address in address1 , address 2 and city .

for ex

address1

flat no 1 , block b , ashok nagar , delhi

i want

address1...... address2..... city
flat no1......ashok nagar.....delhi
block b

You can do it with the help of string functions i.e. LOCATE(), SUBSTRING() and SUBSTRING_INDEX() only if your data is in a specific format for all records. e.g.

flat no 1 , block b , ashok nagar , delhi
flat no 1 , block b [address 1]
ashok nagar [address 2]
delhi [city]

House no 19, block f , Mehrauli , delhi
House no 19, block f [address 1]
Mehrauli [address 1]
delhi [city]

In the above examples, address 1 is till 2nd comma
address 2 after 2nd comma and the city is after 3rd comma.