Reverse string search in Teradata

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.

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.

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!