Recently I needed to parse a full path to a bunch of files to get just the name of the file. To do this I had to use a regular expression and some string manipulation. In doing this I found the use of regular expressions in Oracle Analytics to be very powerful and I thought I would share.

In my examples I focus on project calculations but you can also do this in data preparation or data flows.

I am no regular expression expert so full credit goes to Matthew Jakubiak the architect for our product who pointed me in the right direction.

The key step was to create a new calculated column that modified the file path I had and turned the very last ‘/’ into a series of characters I could use later in string manipulation. The file paths in my source data were formatted like this:

/Library/User Pictures/Flowers/Red Rose.tif

The expression I used was:

regexreplace(File Path, '(\/)(?!.*\1)', '**FOUND**')

This would change the path to have the text ‘**FOUND**’ in place of the last ‘/’:

/Library/User Pictures/Flowers**FOUND**Red Rose.tif

With this in place I could use out of the box string functions like RIGHT, LEFT, and LOCATE to parse the new modified path and get the file name and path separately.

For the file name I used the LOCATE function to find my new unique string and then the RIGHT function to parse out just the name of the file. Notice I have to remove 8 characters because the LOCATE function will return the location of the start of the string.

RIGHT((File Path),(LENGTH((regexreplace(File Path, '(\/)(?!.*\1)', '**FOUND**')))-LOCATE('**FOUND**',(regexreplace(File Path, '(\/)(?!.*\1)', '**FOUND**'))) - 8))

Same idea to break out he path except I used the LEFT function.

LEFT(File Path,Locate('**FOUND**',(regexreplace(File Path, '(\/)(?!.*\1)', '**FOUND**'))))

This is just one example of using regular expressions to manipulate stings but hopefully you can think of other use cases for this powerful function. If you would like to read more about Regular Expressions in Oracle Analytics you can visit this blog on as similar topic. https://blogs.oracle.com/analytics/enhance-your-data-preparation-using-oracle-analytics