I am trying to concatenate 2 fields ACCT_NO and BR_NO where both fields should add up to 12 character and if not I should add leading zeros. concatenation must have a suffix of TDA. For example:
However, the BR_N0 can be one, two, three, or more digits and hence the number of concatenated zeros will change. Here is an example:
I am not sure how to accomplish this since it is dynamically changing, not fixed number of leading zeros.
BR_NO ACCT_NO Number of digits Number of leading zeros to be added Final format should be
363 841555 9 3 000363841555TDA
7 560486 7 5 000007560486TDA
67 513654 8 4 000067513654TDA
253 851530 9 3 000253851530TDA
278 486479 9 3 000278486479TDA
730 905543 9 3 000730905543TDA
725 599600 9 3 000725599600TDA
381 601226 9 3 000381601226TDA
700 664220 9 3 000700664220TDA
676 513107 9 3 000676513107TDA
Sorry, here is a sample data again:
Concatenate the yellow columns to look like the green column. number of leading zeros will change because the Br_No is not fixed:
Ok, I used this function however, here is what I am getting:
BR_NO = 974
ACCT_NO = 242101
As you see, there are spaces before the BR_NO (4 leading spaces)then there are 3 leading spaces before the ACCT_NO and the ACCT_NO last 3 digits were truncated (101). This repeats with all the number.
just fyi, the data type for BR_NO ([NUMBER(6,0)]) and ACCT_NO ([NUMBER(7,0)])
You didn't tell that those columns were numeric.
Concatenation on numerics results in an automatic typecast, which results in leading blanks. You get rid of them when you TRIM:
cast(LPAD(trim(BR_NO) || trim(ACCT_NO), 12, '0') as char(12)) || 'TDA'
This might be a bit more efficient:
(BR_NO * 1000000 + ACCT_NO (format '9(12)')) || 'TDA'
Thank you dnoeth, very much. both work very nicely. just one question, what is the '1000000' in the second method, for?