CODEWITHSUNDEEP
mysql
PHP Freelancer India
PHP Freelancer India

Reputation: 11

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

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 ?

Upvotes: 1

Views: 1702

Answers (1)

lord_nullset
lord_nullset

Reputation: 31

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.

Upvotes: 1

Related Questions