# remove leading and trailing zeros

• ### 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

• 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 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 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

• Proposed as answer by 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.

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 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 Thursday, June 6, 2013 3:53 PM
• Unmarked as answer by 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 Friday, June 7, 2013 3:56 AM
Friday, June 7, 2013 3:56 AM