I'm trying to pull the string to the right of the last forward slash in the string below.
/Applied Analytics/URMFG/Service Analysis/ServiceAnalysis
So basically, I would like to see ServiceAnalysis returned.
I've come across the following SQL, which is close to what I need, but it's not exact.
=MID(K19, FIND("/",K19)+1, LEN(K19))
Regular Expressions to the rescue! you can achieve this using the RXReplace() function:
RXReplace([column],"^/.*/(.*)$","$1","")
I'll let you look up the RXReplace() documentation on your own, but just to explain the regex itself:
^/ matches the beginning of the string and the starting /
.*/ matches any characters that come next, followed by a / which is the final / before the end of the string (and preceeding the bit that we want to extract)
(.*)$ matches any characters that come next, putting them into a "capturing group" (basically a variable), followed by the end of the string
the $1 is a token which refers to the capturing group above (normally this looks like \1 in regex, but Spotfire is a bit different)
pretty much any time you need to deal with extracting bits of strings in Spotfire expressions, RXReplace() is what you want. it's a lot more sustainable than doing a ton of Left()s, Right()s, and Len()s, although the initial effort can be a bit higher.
more regex info at http://www.regular-expressions.info/.