How to use regexp_instr in Teradata such that start position of the search start at the end? Unlike INSTR its not taking -1 as position argument

Question

How to use regexp_instr in Teradata such that start position of the search start at the end? Unlike INSTR its not taking -1 as position argument?I was trying to get the occurrence of a pattern from the end of a string. Unlike INSTR its not taking -1 as position argument.

Answer 1

Regex does not work like that. Instead, you ANCHOR your pattern to the end of the string with $. So if you want all strings that end with XYZZY, your regex is XYZZY$.

EDIT OP revealed that it is the position of the last alphanumeric character that is desired. So, use regex to find one alphanumeric followed by an indefinite number of non-alphanumerics followed by the end of the string.

Using lowest common denominator regexp functions, alphanumeric is [A-Za-z0-9], or, if we remember to turn on the "i" flag in the call we can shorten it to [a-z0-9]. Similarly, a non-alphanumeric is [^a-z0-9], and any possible number of them are [^a-z0-9]*. Finally, the end of the string is $. Putting it all together,

Regexp_instr(mytextcolumn,'[a-z0-9][^a-z0-9]*$',1,1,0,'i')

The zero arg returns the first position of the match.