I needed to perform a reverse string search to find the last occurence of a slash. It took me a while before I found a simple and elegant solution using an Oracle-equivalent function INSTR which’s been around since Teradata 14.
Let’s say we’re getting the full file path from my TPT loading script (‘/data/in/20161020_050000.csv’). However, we’re interested only in the name of the file, rather than it’s physical location during its loading process.
First, we need to get the last slash’s position using the INSTR function.
1 2 | SELECT INSTR('/data/in/20161020_050000.csv','/',-1,1) AS last_slash_index -- Result: 9 |
We take the result, increment it by 1 (to get the first letter after slash) and use it as the SUBSTR function’s start location parameter.
1 2 3 4 | SELECT INSTR('/data/in/20161020_050000.csv','/',-1,1) AS reverse_index, SUBSTR('/data/in/20161020_050000.csv',reverse_index + 1) -- RESULT: 9 | 20161020_050000.csv |
Simple and efficient!
Leave a Reply