-1

I have this table:

+----+----------------------------+---------+----------+
| id | coordenadas                | latitud | longitud |
+----+----------------------------+---------+----------+
|  1 | -50.23232323,-100.74329430 | NULL    | NULL     |
|  2 | 51.506944,-0.1275          | NULL    | NULL     |
|  3 | 19.433333                  | NULL    | NULL     |
+----+----------------------------+---------+----------

I use this query for separate the string by comma and insert into a new field:

UPDATE tabla SET
    -> latitud = SUBSTRING(coordenadas,1,LOCATE(",",coordenadas) - 1);

and works fine.

The problem is the ID:3. Only have a value and it´s not separated by comma. That value don`t insert into the new field (Latitud).

How can I solve this problem?

Thanks

Juan Ignacio Medina
  • 1
  • 1
  • 2

2 Answers2

2

MySQL has the very convenient substring_index() function:

UPDATE tabla
    SET latitud = substring_index(coordenadas, ',', 1);

Longitude is a bit trickier. Here is the code for that too:

UPDATE tabla
    SET latitud = substring_index(coordenadas, ',', 1),
        longitud = (case when coordenadata like '%,%'
                         then substring_index(coordenadas, ',', -1)
                    end);

This will set it to NULL for the third row.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

MySQL is not a programming language.

Use PHP or some other scripting language to make changes like this.

Abhi Beckert
  • 32,787
  • 12
  • 83
  • 110