Derived colum expression with strings removing 0

• david_h_edmonds

  Default port

  Points: 1430

  More actions

  April 12, 2019 at 2:09 pm

  #3157423

  Hi, I am setting up a derived column to combine a variable that is entered to a previous derived column with a dataflow value.

  (DT_I4)@[User::Actual_FileName] in this example = 1000 and becomes the derive column called TMC_Ident

  Branch Key = '072'

  In turn,

  (DT_STR,4,1252)TMC_Ident + (DT_STR,6,1252)Branch_key should therefore = 1000072 but is coming out from this expression as 100072 i.e. removing the leading 0 from the value.

  Any ideas on how to avoid this?

  Should I change (DT_I4)@[User::Actual_FileName] to be (DT_STR,4,1252)User::Actual_FileName] ?

  Cheers

  Dave

• Thom A

  SSC Guru

  Points: 99137

  More actions

  April 12, 2019 at 2:36 pm

  #3158468

  I assume Branch_key is some kind of numerical data type? Numerical data types don't store leading zeros. Assuming that Branch_key should have 6 characters (as you're casting to a (DT_STR,6,1252) the you could use something like this:

  (DT_STR,4,1252)TMC_Ident + RIGHT("000000" + (DT_STR,6,1252)Branch_key,6)

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• david_h_edmonds

  Default port

  Points: 1430

  More actions

  April 12, 2019 at 3:19 pm

  #3160309

  Hiya, thanks for the response.

  i had cast the branch_key as varchar 3 in the select statement but the advanced editor was still showing 4 byte int - all fixed when that was changed.

  Always check the advanced editor people!

  have a great weekend,

  Dave