Post reply

ppend 0''s

  • December 4, 2006 at 8:19 pm

    #116509

    I have to generate an output file with several columns.  One of the business rules requires the TableID to be 5 digits, so if the value is less than 5 digits I would have to add 0's to the beginning.

    Ex: original value = 123  new value = 00123.

    Is it possible to do this with an existing transformation task (Derived Column)?

     

    Thanks again.

  • December 6, 2006 at 7:29 am

    #676209

    yep, you can do this very easily in your activeX script of Data Transformation task, as long as your database column is a char/varchar field

  • December 6, 2006 at 9:18 am

    #676276

    Thanks for the suggestion.  Fortunately, I was able to find a way to do this using a derived column task and expression.

    Example:

    Column    Action               Expression

    TableID Replace(tableID)     (len(tableID) == 3 ? "00" + tableid : TableID

     

    Explanation - the expression above is similar to a case statement.  "?" = if  and ":" = else

    So basically, "(len(tableID) == 3 ?" is the True/False check.  If (len(tableid) == 3  Then "00" + tableID else ":" tableID

  • December 6, 2006 at 12:06 pm

    #676374

    I found a better way to add 0's to the tableID

    Using derived column task, I add a new column with the following expression.

    RIGHT("0000", 5 - (LEN(TableID))) + TableID

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply