Split fields by characters in MySQL

I have a project where the geographical coordinates for records are stored in a single field separated by a comma. While this may suit general display purposes, it eliminates the capability to search by distance.

Sample data: 34.7451738,-86.5941693

My solution was to add two new FLOAT(10, 6) fields named “lat” and “lng” to the database table. Then I ran this query to populate the two new fields with the existing “coordinates” data.

UPDATE `locations` SET `lat` = SUBSTRING_INDEX(SUBSTRING_INDEX(`coordinates`, ',', 1) , ',', -1), `lng` = SUBSTRING_INDEX(SUBSTRING_INDEX(`coordinates`, ',', 2), ',', -1);

Who doesn’t love one-liners for mass-updates in a table 😉

2 thoughts on “Split fields by characters in MySQL

  1. admin Post author

    For those of you who might be looking for a distance calculation query…

    1. “3959” for miles, “6371” for kilometers
    2. Origin latitude “28.18”
    3. Origin longitude “-81.83”
    4. Desired radius “50”
    5. Record limit “10”

    SELECT name, address, ( 3959 * ACOS( COS( RADIANS( 28.18 ) ) * COS( RADIANS( lat ) ) * COS( RADIANS( lng ) – RADIANS( – 81.83 ) ) + SIN( RADIANS( 28.18 ) ) * SIN( RADIANS( lat ) ) ) ) AS distance FROM `location` HAVING distance <50 ORDER BY distance LIMIT 0 , 10

    Reply
  2. admin Post author

    You could of course run your distance queries against the combined coordinates field as well…

    SUBSTRING_INDEX(SUBSTRING_INDEX(`coordinates`, ‘,’, 1) , ‘,’, -1) AS `lat`, SUBSTRING_INDEX(SUBSTRING_INDEX(`coordinates`, ‘,’, 2), ‘,’, -1) AS `lng`

    For my purposes, splitting the field seemed like a better idea at the time.

    Reply

Leave a Reply to admin Cancel reply

Your email address will not be published. Required fields are marked *