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


i wonder if can extract multiple data single query using string operations.?

here looking query returns me

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

can extract lat value , lon value through string query?

i tried , succeed extracting single value.

can suggest me how this?

of-course can after pulling data rails app thinking pull data directly query.

yes done assuming pattern shown same

mysql> select replace(substring_index(substring_index('{"lat"=>"28.644348", "lon"=>"77.219682"','"lat"=>',-1),',',1),'"','') 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),'"','') lon; +-----------+ | lon       | +-----------+ | 77.219682 | +-----------+ 

so can combine both of them single select

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

Comments

Popular posts from this blog

matlab - "Contour not rendered for non-finite ZData" -

delphi - Indy UDP Read Contents of Adata -

javascript - Any ideas when Firefox is likely to implement lengthAdjust and textLength? -