How to split string that contains comma into another column?

I’m trying to split columns for coordinates into their respective lat and lng columns

In the coordinates columns i have these

-74.93360137939453, 40.07080078125
-101.473911, 38.704022
-151.695999146, 59.94919968

but when i run the code below,

UPDATE `data`
      SET `lat`  = TRIM(SUBSTR(`coordinates`,INSTR(`coordinates`,',')-1))
        , `lng` = TRIM(SUBSTR(`coordinates`,INSTR(`coordinates`,',')+1));

it adds it to the columns like this

3, 40.07080078125 | 40.07080078125
1, 38.704022      | 38.704022
6, 59.94919968    | 59.94919968

Answer

I have a solution for getting before the comma and after the comma value from coordinates strings. so, you can use like below code as your requirements.

Definition and Usage:

The SUBSTRING_INDEX() function returns a substring of a string before a specified number of delimiter occurs.

Syntax:

SUBSTRING_INDEX(string, delimiter, number)

Useful Example:

Input SUBSTRING_INDEX("-74.93360137939453, 40.07080078125", ",", 1);

Output -74.93360137939453

Input SUBSTRING_INDEX("-74.93360137939453, 40.07080078125", ",", -1);

Output 40.07080078125