How to explode mysql field value and use it in SELECT query

Question

How can i explode field value of table in select query ?

for e.g. i have 1 field in table named "coordinates" which contains latitude , longitude.

Now i want to use this latitude and longitude in select query.

Can i separate this values and use it in select query ?

http://stackoverflow.com/questions/1096679/can-mysql-split-a-column — ArK, Oct 25 '10 at 07:27
BTW, as far as I understand, that's violation of 1NF (http://en.wikipedia.org/wiki/First_normal_form) — Kel, Oct 25 '10 at 07:32
the database schema should have contained 2 fields - 1 for latitude & 1 for longitude — Knowledge Craving, Oct 25 '10 at 07:44
http://dev.mysql.com/doc/refman/5.0/en/creating-a-spatially-enabled-mysql-database.html have a look there. @kel, i still think you can see a point (x,y or lat, long) as one field. Not necessarily breaking the Codd's rules? — Roger, Aug 20 '12 at 18:31

score 1 · Answer 1 · answered Jan 31 '13 at 19:30

Firstly, the comments are correct: this is a violation of normal form. Always store separate data in separate columns - it will make your life easier.

If you try to write a select statement that parses the coordinates field and tries to filter on one or both halves, you will have a query that runs SUPER slowly, since an index on that column will not function. Instead, I would recommend writing a query to split that column into two, such as the following:

alter table `your_table`
    add column `coordinate_x` int null;
alter table `your_table`
    add column `coordinate_y` int null;
update `your_table`
    set `coordinate_x` = substring(`coordinates`,1,locate(',',`coordinates`))
        ,`coordinate_y`= substring(`coordinates`,locate(',',`coordinates`)+1);
alter table `your_table`
    drop column `coordinates`;
alter table `your_table`
    modify column `coordinate_x` int not null;
alter table `your_table`
    modify column `coordinate_y` int not null;

You could then index coordinate_x and coordinate_y to make your select statement run quickly.

How to explode mysql field value and use it in SELECT query

1 Answers1