TERADATA: How do I use position function to return the position of a substring in a string

I have a string "Rj 0001 201410212014110301G 20141103ioR4564534 N20141021N 11R" in the table. I would like to return the value "R4564534" from the string.
The query I use is:

select SUBSTR(columnX,POSITION('R' IN columnX),8);

but it returns "Rj" instead of "R4564534".
The result I'd like to get is "R4564534".

1 Answer

Is it actually 'Rj', only two characters? Not 'Rj 0001 '?

There's already an 'R' in the first position of your string, of course it's not starting with the 2nd 'R'.

What's yout Teradata release?

In TD14 you can utilize INSTR to start at the 2nd character:

SUBSTR(columnX,INSTR(columnX,'R',2),8) 

If you got more complex rules you might utilize regular expression in REGEXP_SUBSTR, too.