All Forums General
Alsallam 9 posts Joined 04/15
28 Aug 2015
How to create a dynamic concatenation

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:
000007560486TDA
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
Alsallam 9 posts Joined 04/15
29 Aug 2015

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:
 

dnoeth 4628 posts Joined 11/04
29 Aug 2015

There's an LPAD function:

cast(LPAD(BR_NO || ACCT_NO, 12, '0') as char(12)) || 'TDA'

 

Dieter

Alsallam 9 posts Joined 04/15
31 Aug 2015

Ok, I used this function however, here is what I am getting:
 
BR_NO = 974
ACCT_NO = 242101
Results:
    974  242TDA
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)])
 

dnoeth 4628 posts Joined 11/04
31 Aug 2015

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'

 

Dieter

Alsallam 9 posts Joined 04/15
31 Aug 2015

Thank you dnoeth, very much. both work very nicely. just one question, what is the '1000000' in the second method, for?

dnoeth 4628 posts Joined 11/04
31 Aug 2015

Multiplying by 1000000 shifts the BR_NO seven digits to the left before adding the seven digits of ACCT_NO.

Dieter

You must sign in to leave a comment.