Getting a substring in a mysql query

Question

In my mysql table, one of the fields holds data of the nature:

{"gateway":"somevalue","location":"http://www.somesite.org/en/someresource","ip":"100.0.0.9"}

I need to extract the value of the location attribute alone from this field, which is

http://www.somesite.org/en/someresource

in the this case. How do I write a query to achieve this?

That's a very good example (and motivation) of how you should NOT save information into the DB! — Nir Alfasi
– Nir Alfasi, Commented Dec 9, 2013 at 5:55

peterm · Accepted Answer · 2013-12-09 06:01:41Z

3

Apart from the fact that you better off not storing delimited values of any form (including JSON) in the database, but rather normalize your data, you can leverage very handy SUBSTRING_INDEX() function in the following way

SELECT TRIM(BOTH '"' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, '"location":', -1), ",", 1)) location
  FROM table_name
 WHERE ...

edited Dec 9, 2013 at 6:01

answered Dec 9, 2013 at 5:53

peterm

93k16 gold badges157 silver badges162 bronze badges

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

Collectives™ on Stack Overflow

Getting a substring in a mysql query

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