I habe a varchar(5) column, holding a combination text and numbers, manly 6 char spaces seperated by a colon. The can be cases where there is only one occurance, or several of the 6 space colon pattern. I want to go to the very end of the string, and count backwards 6 spaces, and select that value out.
An example of the data:
I'm Teradata Version 14.00.07.15
Sel substr(custnbr(, characters(custnbr) -6) from tablename; runs but brings back 7 spaces, and data would look like :cc9012
I suspect that the characters usage is not properly dealing with numbers in the 6 space count.
Can someone please help me solve this issue.
CHARACTERS/CHAR_LENGTH returns the length of the string, i.e. SUSBSTRING(str FROM CHAR_LENGTH(str)) starts at the last character and now you count 6 characters back.
Thank you Dieter, I was able to make this work. Oddly enough, to properly bring back a backwards count of 6, I had to use 5, 6 would still bring back a count of 7 spaces from the the end
, substr(trim(custnbr), char_length(custnbr) -5)
from table_name group by 1;
Of course you need 5 to get the last 6 characters:
char[char_length] = '8'
char[char_length - 1] = '78'
char[char_length - 2] = '678'
char[char_length - 3] = '5678'
char[char_length - 4] = 'b5678'
char[char_length - 5] = 'bb5678'
char[char_length - 6] = ':bb5678'
Makes sense, it's where to start the count, count the starting position of 0 as 1, out for 5 collects 6 spaces total. Thank you