Jirka's Public Notepad

Data Engineering | Python | SQL Server | Teradata

November 23, 2016 By Jiří Hubáček Leave a Comment

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!

Related

Filed Under: Teradata Tagged With: function, SQL, Teradata

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  • GitHub
  • LinkedIn
  • RSS
  • Twitter
© 2022 · Jiří Hubáček, PGP