Substr - End of Line, Count backwards onText and Numbers

Database
Enthusiast

Substr - End of Line, Count backwards onText and Numbers

Hello,

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:

aa1234

aa1234:bb5678

aa1234:bb5678:cc9012

aa1234:bb5678:cc9012:dd3456

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.

Thank you.

4 REPLIES
Senior Apprentice

Re: Substr - End of Line, Count backwards onText and Numbers

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.

Enthusiast

Re: Substr - End of Line, Count backwards onText and Numbers

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

sel custnbr

, substr(trim(custnbr), char_length(custnbr) -5)

from table_name group by 1;

Senior Apprentice

Re: Substr - End of Line, Count backwards onText and Numbers

Of course you need 5 to get the last 6 characters:

'aa1234:bb5678'

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'
Enthusiast

Re: Substr - End of Line, Count backwards onText and Numbers

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