Need help parsing a string

[cmichailovlee]

I'm trying to parse a string within a field. Specifically, the field captures the referring URL from which the traffic was sent to our site (i.e. Google) and I need to parse out the value of the keyword so I can sort on that. I've not found an easy way to do that. A typical entry is like this:

http://www.google.com/search?q=keyword+string&start=0

What I need to do is build a formula that pulls out what "q" is equal to so that I can then sort on those values.

Anyone able to help me out there?

 

[hilfy]

Try something like this:

numberVar i;
numberVar j;
i := Instr({table.field}, 'q=');
j := Instr(i, {table.field}, '+');
substr({table.field}, i + 2, j - 1);

-Dell



A computer only does what you actually told it to do - not what you thought you told it to do.

 

[cmichailovlee]

Thanks. However, I get an error in the formula on the last line: "The remaining text does not appear to be part of the formula."

substr({table.field}, i + 2, j - 1);

I'm not following the logic behind this formula...what is it attempting to do?

 

[synapsevampire]

Or try:

split(split({table.field},"q=")[2],"+")[1]

-k

 

[cmichailovlee]

For this suggestion, I get this error:

split(split({table.field},"q=")[2],"+")[1]

"A subscript must be between 1 and the size of the array.

 

[lbass]

Please clarify whether you ONLY want the keyword, or whether you want everything after "q=". Also, is there always a "q=" in the field and always a "+"?

-LB

 

[cmichailovlee]

I only want the keyword, yes. The keyword is ALWAYS identified in the Google string by a "q="...for each word that was used in the search, it is always separated by a "+". So there could be multiple instances of "+" after the "q="

I want everything that is equal to the "q="...if I say "everything after the 'q='", then I'll be picking up additional query parameters that I don't need, like the "start=0" value listed above. Just whatever is between the "q=" and the "&" symbol that indicates another query parameter.

 

[cmichailovlee]

For clarification purposes, the following keywords I would need:

q=space+program&start=0
(I would want to pull out space+program)

q=dog+food+stores&start=0
(I would want to pull out dog+food+stores)

q=puppies&start=0
(I would want to pull out puppies)

Does that help to clarify things?

 

[lbass]

Try:

extractstring({table.field},"q=","&")

-LB

 

[cmichailovlee]

JACKPOT!!! Perfect! Thank you for your help.