All Forums Database
31_resu 38 posts Joined 07/13
27 Jan 2016
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.
 

dnoeth 4628 posts Joined 11/04
27 Jan 2016

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.

Dieter

31_resu 38 posts Joined 07/13
28 Jan 2016

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;

dnoeth 4628 posts Joined 11/04
28 Jan 2016

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'

 

Dieter

31_resu 38 posts Joined 07/13
28 Jan 2016

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

You must sign in to leave a comment.