How to create a dynamic concatenation

General
Enthusiast

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

Tags (1)
6 REPLIES
Enthusiast

Re: How to create a dynamic concatenation

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:

Senior Apprentice

Re: How to create a dynamic concatenation

There's an LPAD function:

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

Re: How to create a dynamic concatenation

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)])

Senior Apprentice

Re: How to create a dynamic concatenation

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'
Enthusiast

Re: How to create a dynamic concatenation

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

Senior Apprentice

Re: How to create a dynamic concatenation

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