How to get the last four characters in a field when the string varies

[jaymoore1756]

I am trying to get the last 4 characters in a field that is 14 characters in length. I have tried all sort of ways but none have been successful. Here is what I am currently working with ...

data element test 5 contains

CAST([Letter].[AR00500T BUS].[BANK_ACCOUNT_NO] AS CHAR (14))

It yields this

04763173
7005379878
0432528503
138113446119
00000001078675

Data element test6 has this

(RIGHT([Letter].[AR00500T BUS].[TEST5] ,4))

which yields

blank
blank
blank
19
8675

How can I adjust this to get the last four of each line ?

[Lynn]

Try casting to VARCHAR(14) instead of CHAR(14)

[jaymoore1756]

No change in the data returned

[Lynn]

Ok, how about an rtrim function to remove trailing blanks?

Do you know what data type the bank account number is?

[vanlang]

Hi Jay,

If I'm understanding the situation correctly, you just want the last 5 characters.

This should work:

substring([FIELD NAME],char_length([FIELD NAME]),5)

[Lynn]

Hi Lynn,

If I'm understanding the situation correctly, you just want the last 5 characters.

This should work:

substring([FIELD NAME],char_length([FIELD NAME]),5)

It is jaymoore1756 who is looking for the solution. I think the problem is that he's got some blank padding at the end of his field, so I think those have to be eliminated in order for any string function to return the correct result.

Also, I think substring wants the field, the starting position, and the number of characters as arguments. To use substring to grab the last four characters I think it would need to be:

substring( rtrim( [FIELD NAME] ), ( char_length ( rtrim ( [FIELD NAME] ) ) - 3) , 4 )

[jaymoore1756]

Thanks Lynn I have used your responses to get the data field to work. I appreciate the time and the effort !!!!

[jaymoore1756]

This was the final code in the field  --

Convert
RTRIM(CAST([Letter].[AR00500T BUS].[BANK_ACCOUNT_NO_CALC] AS VARCHAR (14)))

then last 4 of account

(RIGHT([Letter].[AR00500T BUS].[BANK_ACCOUNT_NO] ,4))