Need to extract a string from a URL of varying lengths 1

[elsenorjose]

I have a field which contains a URL with query string arguments. The URL length can vary so the position of the text I need to retrieve can vary. Here is a sample URL:

http://130.81.64.25/D/1837/19971/v0...&SITE_ID=1674&STATION_ID=WAXQ-FM?MSWMExt=.asf

In that URL, you can see the following text: 'MNM=2'

I need to extract only that text from the URL. How do I accomplish this?

Thank you.

 

[audiopro]

What language are you using?
One way would be to split the string to an array on 'MNM=' then read the value from the start of the second element.
The split string would not be allowed anywhere else in the URL

Keith

http://www.studiosoft.co.uk

 

[guelphdad]

you say the URL length can vary, is it possible that it is in the same position each time?

also are you searching for MNM or some other value in that same spot?

You can use SUBSTRING_INDEX to split your strings on the & character for instance and split it again to get what you need.

Might be faster to explode in PHP though.

 

[elsenorjose]

I'm trying to implement this using just SQL so I can hand off the query to a client along with an Excel output of the query.

 

[elsenorjose]

Sorry I didn't answer all the questions.

The position of the MNM string might also change. Those arguments are user configurable so theoretically, at some point in the future some of those arguments might no longer exist causing the position of that string to shift. The constant is the encoding. It will always be preceded by the'&' sign and always have '=' after it. I actually DO need to get the whole string though so what I really need is to extract 'MNM=x' The number defines whether the stream was Music or NonMusic.

 

[johnwm]

Looks like a simple Substring query using the results of a Locate query will do it directly. I've truncated the strings here for readability - it does work on the whole string!

SELECT substring( 'CPROG=SIMULCAST&MARKET=NEWYORK-NY&MNM=2&NG_FORMAT=classicrock', (SELECT LOCATE( 'MNM', 'CPROG=SIMULCAST&MARKET=NEWYORK-NY&MNM=2&NG_FORMAT=classicrock' ) ) , 5
)

This returns :
MNM=2

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[r937]

1. use SUBSTRING_INDEX to split the string on MNM= and take whatever is to the right of it (this includes whatever MNM is equal to, plus everything after it)

2. use SUBSTRING_INDEX to split the result of 1 on an ampersand, and take everything to the left of it (this isolates the MNM value)

select substring_index(
       substring_index(url,'MNM=',-1)
                          ,'&',1) as mnm

result: 2



r937.com | rudy.ca

 

[guelphdad]

that sounds familar r937, I just don't want to take credit. I wouldn't have suggested it in this thread had you not shown it to me. But hey I was only pointing out what to use in the thread so the OP could read about it in the manual first.

 

[elsenorjose]

John, thanks your query did the trick. I just needed to tweak it a little.

SELECT
substring( cs_url,
(SELECT LOCATE( 'MNM', cs_url ) ) , 5
)
from live_play_table

Thanks everyone for the help!

 

[r937]

so you'll never have MNM=10, right?

that embedded SELECT looks weird, too ...

r937.com | rudy.ca

 

[elsenorjose]

No, never a 10. It's either Music (1) or Non-Music(2).

 

[johnwm]

The embedded Select Locate simply returns an integer for the Substring function. Yeah - looks odd but it is valid usage.

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints