CODEWITHSUNDEEP
mysql
Ossi
Ossi

Reputation: 808

Split column data for 2 new columns in MYSQL

I have column "position" in Mysql table1 that has data "N5241 E15601"

Im trying to split this in to 2 existing columns: lat and lon: "N5241" and "E15601"

I tried this code:

 INSERT INTO table1 (lat, lon)
SELECT
    SUBSTRING_INDEX(Position, ' ', 1) AS lat,
    SUBSTRING_INDEX(SUBSTRING_INDEX(Position, ' ', 2), ' ', -1) AS lon
FROM table1;

Above will add new rows, and will not update lat and lon column (Currently NULL)

I tried UPDATE, but I get errors.

I know this is easy for most of you, but Im still newbie...

Thanks Hank

Upvotes: 0

Views: 25

Answers (1)

Nick
Nick

Reputation: 147166

You should be able to use a simple UPDATE:

UPDATE table1
SET lat = SUBSTRING_INDEX(Position, ' ', 1),
    lon = SUBSTRING_INDEX(SUBSTRING_INDEX(Position, ' ', 2), ' ', -1)

Note if your Position field is always lat lon with no extra spaces around it you can simplify that to 

UPDATE table1
SET lat = SUBSTRING_INDEX(Position, ' ', 1),
    lon = SUBSTRING_INDEX(Position, ' ', -1)

Demo on dbfiddle

Upvotes: 1

Related Questions