sql - How do you do multiple substrings for a field in Teradata?

Question

I have a field to pull account numbers which have different lengths and I want to pass the last four digits of the account number. The dilemma I am having is that since they are different lengths I am having trouble in substringing the fields. The standard length is 11 digits but there are accounts with 9 digits and 7 digits.

How do I substring those values in multiple substrings to capture all the account last 4 digits in one query?

This currently what I have:

SELECT  SUBSTRING(ACCT_NBR,7,4) AS BNK_ACCT_NBR
FROM NAMEOFTABLE;

I want to have additional substrings to capture the account numbers that don't have 11 digits similar to

SUBSTRING(ACCT_NBR,5,4)
SUBSTRING(ACCT_NBR,4,4) 

The results should look like:

76587990891 - 0891 654378908 - 8908 45643456 - 3456

Can you please help me in figuring out how I can do that?

Thanks.

question from:https://stackoverflow.com/questions/65923010/how-do-you-do-multiple-substrings-for-a-field-in-teradata

Answer 1

Is ACCT_NBR a VarChar or an INT?

VarChar:
Right (ACCT_NBR,4)
Substr(ACCT_NBR,Char_Length(x)-3)

INT:
ACCT_NBR MOD 10000