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 be363 841555 9 3 000363841555TDA7 560486 7 5 000007560486TDA67 513654 8 4 000067513654TDA253 851530 9 3 000253851530TDA278 486479 9 3 000278486479TDA730 905543 9 3 000730905543TDA725 599600 9 3 000725599600TDA381 601226 9 3 000381601226TDA700 664220 9 3 000700664220TDA676 513107 9 3 000676513107TDA`

Tags (1)
6 REPLIES 6
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:

## Re: How to create a dynamic concatenation

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

## 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?