can we extract multiple data from a single query using string functions in mysql?

Question

I am wonder if i can extract multiple data with a single query using string operations.?

Here is what i am looking for My query returns me

+-----------------------------------------+
|               geodata                   |
+-----------------------------------------+
| {"lat"=>"28.644348", "lon"=>"77.219682" |
+-----------------------------------------+

Can i extract lat value and lon value through any string query?

i tried and succeed up to extracting a single value.

can anyone suggest me how to do this?

of-course i can do this after pulling this data to my rails app but i am thinking to pull this data directly from the query.

select replace(substring_index('{"lat"=>"28.644348", "lon"=>"77.219682" ','"',-2),'"',''); — Aparichith
– Aparichith, Commented Nov 27, 2014 at 13:28

Abhik Chakraborty · Accepted Answer · 2014-11-27 13:27:45Z

1

Yes it could be done assuming that the pattern you shown is same

mysql> select replace(substring_index(substring_index('{"lat"=>"28.644348", "lon"=>"77.219682"','"lat"=>',-1),',',1),'"','') as lat;
+-----------+
| lat       |
+-----------+
| 28.644348 |
+-----------+
1 row in set (0.00 sec)

mysql> select replace(substring_index(substring_index('{"lat"=>"28.644348", "lon"=>"77.219682"','"lon"=>',-1),',',1),'"','') as lon;
+-----------+
| lon       |
+-----------+
| 77.219682 |
+-----------+

So finally you can combine both of them into a single select

select 
replace(substring_index(substring_index(geodata,'"lat"=>',-1),',',1),'"','') as lat,
replace(substring_index(substring_index(geodata,'"lon"=>',-1),',',1),'"','') as lon
from your_table

answered Nov 27, 2014 at 13:27

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

can we extract multiple data from a single query using string functions in mysql?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related