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 ?
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.
