how to split the values of a mysql column delimited by a value into another column?

Question

I have a column containing house numbers and unit numbers. I need the house numbers and unit numbers in two different columns.

The unit numbers are contained within brackets. The following is the sample data:

Column 1           Column 2
85 (Unit A)
85 (Unit B)
86
87 - 89 (Unit A)

The desired result is as follows:

Column 1          Column 2
85                Unit A)
85                Unit B)
86      
87 - 89           Unit A)

The values are delimited by '('

How do I do this?

EDIT

UPDATE your_table 
SET column2 = SUBSTRING_INDEX(your_table.column1,'(', -1), 
column1 = SUBSTRING_INDEX(your_table.column1,'(', 1)

The result of this query is as follows:

Column 1          Column 2
85                Unit A)
85                Unit B)
86                86
87 - 89           Unit A)

I don't need the '86' Column 2 to be updated! I want it to remain as null if there is no '(' How do I do this?

Answer 1

For selection,

SELECT  SUBSTRING_INDEX( Col1 , '(', 1 ) AS C1,SUBSTRING_INDEX(SUBSTRING_INDEX( Col1 , '(', 2 ),'(',-1) AS C2  from Tablename

For update

Update TableName set Column1= SUBSTRING_INDEX( Column1 , '(', 1 ) ,Column2= SUBSTRING_INDEX(SUBSTRING_INDEX( Column1 , '(', 2 ),'(',-1) ;