select part of url string in MySQL

Question

If have the following string with a unique element in it. This unique element I need to extract from this string and save it as an variable. The part that I need to extract is: Proto-1088.

"{"account":"123456","n":{"Score":"Bad","Ticket":"https://test.test2.com/browse/Proto-1088"},"2":{"Score":"GOOD"}}

I have tried to use the TRIM function in MySQL but this didn't work since the number in Proto changes.

What version of MySQL? 5.7 has JSON functions that would be happy to do the task. — Rick James
– Rick James, Commented Jun 8, 2017 at 22:57

user2314737 · Accepted Answer · 2017-06-07 11:09:31Z

1

If that's the only URL in the string you could use SUBSTRING_INDEX

SELECT 
SUBSTRING_INDEX(
  SUBSTRING_INDEX(
  '"{"account":"123456","n":
  {"Score":"Bad","Ticket":"https://test.test2.com/browse/Proto-1088"},"2":{"Score":"GOOD"}}', 
  '/', -1),
'"',1)

Explanation:

SUBSTRING_INDEX(str, delim, count) finds the index of count occurrence of delim (counting from left if count is positive, from right if negative) and returns everything past that index (going left if count is positive, right if negative).

So I used SUBSTRING_INDEX with count=-1 to extract everything to the right of the last occurrence of '/' and then took everything to the right of the first occurrence of '" using count=1.

edited Jun 7, 2017 at 11:09

answered Jun 7, 2017 at 10:48

user2314737

29.7k20 gold badges109 silver badges126 bronze badges

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

Collectives™ on Stack Overflow

select part of url string 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