Removing Substring in Load Script

[#1]

Hello,

  How would I go about removing a substring whenever I find a specific pattern in a field in the load script.

Example:

     If the string is http://www.internet.com/specificpage.cfm?id=12345

     I would want to grab the '12345' everytime there is '/specificpage.cfm?id='

     Even if the url was http://www.internet.com/specificpage.cfm?id=12345&other=stuff

So I am trying to find a specific pattern, note its place in the string, and then make a substring with the 5 characters after that position... I think

I have gotten this far before getting stumped:

load

IF(wildmatch(URL,'*/specificpage.cfm?id=*')=1,SUBSTRING STUFF HERE,0) as id_substring;

SELECT

etc...

Thanks!,

Tyler

[swuehl · Accepted solution]

try using index function to get the position of your substring, like

as SUBSTRING STUFF HERE:

mid(URL,index(URL,'/specificpage.cfm?id=')+21, 5)

where 21 is the count of characters in your searchstring (hope I counted correctly).

Hope this helps,

Stefan

[#3]

Works, thanks!