Derived Column

I have two columns made up of 4 digits numbers eg col1 1234, col2 9876

I want to create a derived column so i get a eight digit column, eg col3 12349876

I cannot seem to get the expression right, I seem to be always adding the value which i do not want, can someone help me out with the expression

Thanks

Robbie

if you just use the '+' operator, the values would get added. To fix that just cast the values to string before concatenating them.

something like:

(DT_STR,4,1252)col1 + (DT_STR,4,1252)col2

|||

Worked like a dream thanks,

One day I will understand all this !!!!

|||test|||test agauin|||test gain, this must be my last|||

Please mark the thread as answered...

thanks

|||

Note that if your 4-digit numbers could begin with one or more zeros, you would need to do a bit more to ensure the result is 8 digits. For example, 1234 and 0056 would result in 123456 using the above expression. If you actually want 12340056, you would need to do something like this:

RIGHT("0000" + (DT_WSTR,4)col1, 4)) + (RIGHT("0000" + (DT_WSTR,4)col2, 4)

Wrap that whole expression in a cast back to an integer type if that is what you need.

|||

Rafael Salas wrote:

Please mark the thread as answered...

thanks

This is done how?

|||

Mark Durley wrote:

Note that if your 4-digit numbers could begin with one or more zeros, you would need to do a bit more to ensure the result is 8 digits. For example, 1234 and 0056 would result in 123456 using the above expression. If you actually want 12340056, you would need to do something like this:

RIGHT("0000" + (DT_WSTR,4)col1, 4)) + (RIGHT("0000" + (DT_WSTR,4)col2, 4)

Wrap that whole expression in a cast back to an integer type if that is what you need.

Thanks mark this is great stuff, I have another problem but will create a new thread

|||

Personally, I would use

(col1 * 10000) + col2

- If you want to keep the leading zero's, that is.

Pipo1

|||

Pipo1 wrote:

Personally, I would use

(col1 * 10000) + col2

- If you want to keep the leading zero's, that is.

Pipo1

A much simpler (and more efficient) solution, especially if you want to end up with a number still :)

|||

Mark Durley wrote:

Pipo1 wrote:

Personally, I would use

(col1 * 10000) + col2

- If you want to keep the leading zero's, that is.

Pipo1

A much simpler (and more efficient) solution, especially if you want to end up with a number still :)

I may be wrong...but that expression will keep leading zeros for col2 only...what if you want to keep the leading zeros of col1?

|||

Rafael Salas wrote:

I may be wrong...but that expression will keep leading zeros for col2 only...what if you want to keep the leading zeros of col1?

You'll lose them. That's why I like using the right() function.|||

Phil Brammer wrote:

Rafael Salas wrote:

I may be wrong...but that expression will keep leading zeros for col2 only...what if you want to keep the leading zeros of col1?

You'll lose them. That's why I like using the right() function.

Thanks Phil for confirming that. It is good to know I am not crazy