locked
remove leading and trailing zeros RRS feed

  • Question

  • I am trying to import a .txt file using SSIS, this file has a field named "Account Numbers"

    each account number is having 3 leading zeros and 5 trailing zeros. In the final import I want to see just the account number without the leading and trailing zeros.

    Can anyone suggest an easy fix for this issue?

    Wednesday, June 5, 2013 2:02 PM

Answers

  • Mr dhanunjay,

                          You can use ssms instead of ssis for this scenario, use rtrim,lrim. I guess you already got the answer.

    Thanks.

    Muralidhar

    • Marked as answer by DJ Rele Thursday, June 6, 2013 3:37 PM
    Wednesday, June 5, 2013 8:25 PM
  • okay i finally got the whole thing figured out...

    lets take 3 examples of account numbers

    000000123040000000 desired: 1230400

    000123045000000 desired: 1230450

    000000000123045600000 desired: 1230456

    we can see that they have unequal number of leading zeros and we know that the actual number of trailing zeros is 5

    I will show you a step by step process that i followed to fix this problem

    i) Precautionary use of TRIM to get rid of any spaces

    ii) Replace "0"," "

    iii) LTRIM

    iv) Replace " ","0"

    123040000000

    123045000000

    123045600000

    v) Replace "0000000", "x"

    vi) Replace "000000","y"

    12304x (all numbers with 7 zeros will have x in the end)

    123045y (all numbers with 6 zeros will have y in the end)

    123045600000 (all numbers with EXTRA 5 zeros will be unaffected)

    vii) Replace "0"," "

    viii) RTRIM

    ix) Replace " ","0"

    12304x

    123045y

    1230456

    x) Replace "x","00"

    xi) Replace "y","0"

    1230400

    1230450

    1230456

    • Marked as answer by DJ Rele Friday, June 7, 2013 3:56 AM
    Friday, June 7, 2013 3:56 AM

All replies

  • Use a derived column to replace the source column using following expression:

    REVERSE(SUBSTRING(REVERSE(SUBSTRING(Value,4,LEN(Value) - 3)),6,LEN(Value) - 5))

    Note: Value is the source field name and it removes 3 leading 0's and 5 trailing 0's

    Nitesh Rai- Please mark the post as answered if it answers your question

    • Proposed as answer by Harry Bal Wednesday, June 5, 2013 5:59 PM
    Wednesday, June 5, 2013 2:17 PM
  • There are many ways to solve this

    1. You can use the Fixed Width Format if you are exporting only one column 

    2. You can add a derived column and use expression like this to resolve this

    SUBSTRING(SUBSTRING(ACCNO,4,LEN(ACCNO)),1,LEN(SUBSTRING(ACCNO,4,LEN(ACCNO))) - 5)

    ...There are other ways also


    Vikash Kumar Singh || www.singhvikash.in

    Wednesday, June 5, 2013 2:28 PM
  • Hey Nitesh,

    you have given me a good start. now I was wondering if I could remove 5 trailing zeros and an unknown number of leading zeros?

    Wednesday, June 5, 2013 2:36 PM
  • Hey Nitesh,

    you have given me a good start. now I was wondering if I could remove 5 trailing zeros and an unknown number of leading zeros?

    Hi,

    Replace zeros with space - > Trim(LRTIM & RTRIM) - > Replace space with zeros

    I have shown an example using t-sql on how to remove leading & trailing zeros .

    I am sure you can implement this logic to handle the scenario "remove 5 trailing zeros and an unknown number of leading zeros"

    DECLARE @tmp TABLE (AccountNumber INT)
    INSERT @tmp SELECT 010100000
    INSERT @tmp SELECT 0010200000
    INSERT @tmp SELECT 00010300000
    INSERT @tmp SELECT 000010400000
    INSERT @tmp SELECT 00000010500000
    SELECT REPLACE(RTRIM(LTRIM(REPLACE(AccountNumber,'0',' '))),' ','0') AccountNumber 
    FROM @tmp

    We do have REPLACE,LTRIM,RTRIM functions in SSIS - http://msdn.microsoft.com/en-us/library/ms141671.aspx

    REPLACE(RTRIM(LTRIM(REPLACE(AccountNumber,'0',' '))),' ','0')

    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.



    Wednesday, June 5, 2013 3:12 PM
  • Hey Nitesh,

    you have given me a good start. now I was wondering if I could remove 5 trailing zeros and an unknown number of leading zeros?

    You can reverse the source column and get the string without first 5 0's and reverse it once again as: Reverse(Substring(Column,6,Len(Column)-5)). This will remove 5 trailing 0's but leading 0's are still present. After this you can cast this to a integer or bigint data type which will remove any number of leading 0's. Final expression:

    (DT_I8)REVERSE(SUBSTRING(REVERSE(Value),6,LEN(Value) - 5))

    But  the above expression would fail if column [Value] contains some non-numeric characters. You can configure the error op of the derived column to redirect such records.


    Nitesh Rai- Please mark the post as answered if it answers your question


    Wednesday, June 5, 2013 3:17 PM
  • Mr dhanunjay,

                          You can use ssms instead of ssis for this scenario, use rtrim,lrim. I guess you already got the answer.

    Thanks.

    Muralidhar

    • Marked as answer by DJ Rele Thursday, June 6, 2013 3:37 PM
    Wednesday, June 5, 2013 8:25 PM
  • I got an error message, do you know why this error popped up?

    Error at Data Flow Task [TRIM AccNum [1006]]: Attempt to find the input column named "AccountNumber" failed with error code 0xC0010009. The input column specified was not found in the input column collection.

    Error at Data Flow Task [TRIM AccNum [1006]]: Attempt to parse the expression "REPLACE(RTRIM(LTRIM(REPLACE(AccountNumber,'0',' '))),' ','0')" failed and returned error code 0xC00470A2. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.

    Error at Data Flow Task [TRIM AccNum [1006]]: Cannot parse the expression "REPLACE(RTRIM(LTRIM(REPLACE(AccountNumber,'0',' '))),' ','0')". The expression was not valid, or there is an out-of-memory error.

    Error at Data Flow Task [TRIM AccNum [1006]]: The expression "REPLACE(RTRIM(LTRIM(REPLACE(AccountNumber,'0',' '))),' ','0')" on "input column "Account Number" (1015)" is not valid.

    Error at Data Flow Task [TRIM AccNum [1006]]: Failed to set property "Expression" on "input column "Account Number" (1015)".

    Thursday, June 6, 2013 2:09 PM
  • check properly the column name?

    is it account number or account_number

    i think if it is account number call this in brackets like this [account number]

    Thursday, June 6, 2013 2:16 PM
  • I have run into another problem i think.

    By using the expression you suggested, I have also unwantingly removed the zeros from account numbers ending in zero...

    Works great for leading zeros but while removing trailing zeros I am unwantingly removing zeros that are actually useful

    Thursday, June 6, 2013 2:45 PM
  • I just figured out a way to do this, or i think i have...

    I am giving you an example for how It works with that expression and how it works with my modification.

    Initially in my 1st post I said I had 5 trailing zeros and unknown number of leading zeros

    So lets say we come across account number 0000123045000000

    So your solution would result to 123045

    but what I would actually want is 1230450 (notice the sixth zero in the acc number)

    ** I modified your solution to meet this end

    Step 1: Replace([Account Number], "00000", "_")

    Result: 0000123045_0

    Step 2: Replace([Account Number], "_0", "0")

    Result: 00001230450

    Step 3: Replace([Account Number], "0", "_")

    Result: ____123_45_

    Step 4: LTRIM

    Result: 123_45_

    Step 5: Replace([Account Number], "_", "0")

    Result: 1230450

    So far I cant think of any problems that this would cause.

    Can you think of any problems that this might cause?

    • Marked as answer by DJ Rele Thursday, June 6, 2013 3:53 PM
    • Unmarked as answer by DJ Rele Friday, June 7, 2013 3:40 AM
    Thursday, June 6, 2013 3:32 PM
  • you can iterate till you get rite result. you are on the right track.
    Thursday, June 6, 2013 3:38 PM
  • okay i finally got the whole thing figured out...

    lets take 3 examples of account numbers

    000000123040000000 desired: 1230400

    000123045000000 desired: 1230450

    000000000123045600000 desired: 1230456

    we can see that they have unequal number of leading zeros and we know that the actual number of trailing zeros is 5

    I will show you a step by step process that i followed to fix this problem

    i) Precautionary use of TRIM to get rid of any spaces

    ii) Replace "0"," "

    iii) LTRIM

    iv) Replace " ","0"

    123040000000

    123045000000

    123045600000

    v) Replace "0000000", "x"

    vi) Replace "000000","y"

    12304x (all numbers with 7 zeros will have x in the end)

    123045y (all numbers with 6 zeros will have y in the end)

    123045600000 (all numbers with EXTRA 5 zeros will be unaffected)

    vii) Replace "0"," "

    viii) RTRIM

    ix) Replace " ","0"

    12304x

    123045y

    1230456

    x) Replace "x","00"

    xi) Replace "y","0"

    1230400

    1230450

    1230456

    • Marked as answer by DJ Rele Friday, June 7, 2013 3:56 AM
    Friday, June 7, 2013 3:56 AM