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 ?
Try casting to VARCHAR(14) instead of CHAR(14)
No change in the data returned
Ok, how about an rtrim function to remove trailing blanks?
Do you know what data type the bank account number is?
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)
Quote from: vanlang on 25 Oct 2013 09:39:29 AM
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 )
Thanks Lynn I have used your responses to get the data field to work. I appreciate the time and the effort !!!!
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))