Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Length func expression in ssis derived column

Answered Length func expression in ssis derived column

  • Sunday, April 01, 2012 12:24 AM
     
     
    Sign In to Vote
    0

    Hi All,

    Can anybody tell me the mistake in this derived column expression..here amount data type is varchar(8).

    I want to display amount as 00091900 if I get 91900

    ((LEN([Amount])==1?"0000000"+[Amount]:[Amount]):(LEN([Amount])==2?"000000"+ [Amount]: [Amount]))

    Thanks,

    RH

    sql

     

All Replies

  • Sunday, April 01, 2012 12:36 AM
     
     Answered Has Code
    Sign In to Vote
    0

    I am going to assume that Amount is varchar(8)

    Try the following expression:

    RIGHT("00000000" + [Amount]  , 8 )

    Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008

     
  • Sunday, April 01, 2012 2:22 PM
     
     
    Sign In to Vote
    0

    Hello,

    Below links will help you.

    http://sqlage.blogspot.in/2011/03/remove-leadingtrailing-or-both-zeros-in.html

    http://stackoverflow.com/questions/9614897/removing-leading-zeros-in-ssis


    http://stackoverflow.com/questions/9614897/removing-leading-zeros-in-ssis
    • Edited by willsonyano Sunday, April 01, 2012 2:22 PM
    •  
     
  • Sunday, April 01, 2012 5:27 PM
    Moderator
     
     Answered
    Sign In to Vote
    0
    Two possible expressions for leading zeros:
    • RIGHT(("00000000" + [STRING_COLUMN]), 8)
    • REPLICATE("0", 8 - LEN([STRING_COLUMN])) + [STRING_COLUMN]
    And if the column is not a string column then you have to add a CAST, something like: (DT_STR,8,1252)

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter