Answered by:
leading zeroes

Question
-
i have to import an excel table to SQL. The column has values like this.
1
001,
31,
35,
031
035.
Some of them are left padded with zeroes. Some are not. Whether they are left padded or not, i should left pad with Zeroes and send to SQL. How to do it.
Thanks.
NSG12Wednesday, August 26, 2009 9:12 PM
Answers
-
- Proposed as answer by Manish Sharma - ETL Thursday, August 27, 2009 5:32 AM
- Marked as answer by Todd McDermid Thursday, August 27, 2009 6:12 PM
Wednesday, August 26, 2009 10:50 PM -
I have another expression which will work:
REPLICATE("0",15 - LEN(Column1)) + Column1
15 is the columns width that u want.
Good to know different ways of doing things.
Hope this helps !! Please close the threads once answered - Sudeep- Proposed as answer by Nitesh Rai Thursday, August 27, 2009 9:28 AM
- Marked as answer by Todd McDermid Thursday, August 27, 2009 6:12 PM
Thursday, August 27, 2009 9:26 AM
All replies
-
could you please post the output of the same sample data?
Nitesh Rai- Please mark the post as answered if it answers your questionWednesday, August 26, 2009 9:17 PM -
The output should be,
001,
002,
031,
036
031,
037.
If the no. has no leading zeroes , it should add a zero.
NSG12Wednesday, August 26, 2009 9:19 PM -
Take the flat file source.
Then a script componet.
Input file: Required Output
------------------------------------------
1 01
2 02
001 001
09 09
Let the Source Column Name is Id
Inside script component create a new output column (NewId).
I have written following code in script componet
Inherits
UserComponent
Dim A As String
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
A = "0"
If Row.ID.StartsWith(A) Then
Row.NewId = Row.ID.ToString()
Else
Row.NewId = A.ToString() + Row.ID.ToString()
End If
End Sub
Nitesh Rai- Please mark the post as answered if it answers your question- Proposed as answer by Nitesh Rai Wednesday, August 26, 2009 9:59 PM
- Unproposed as answer by Todd McDermid Thursday, August 27, 2009 6:13 PM
Wednesday, August 26, 2009 9:35 PM -
Thanks. But, instead of Script Component, can something be done in Derived Column.
NSG12Wednesday, August 26, 2009 9:47 PM -
Derived Column name Expression
--------------------------------------------------
NewId FINDSTRING(ID,"0",1) == 1 ? ID : "0" + ID
Nitesh Rai- Please mark the post as answered if it answers your question- Proposed as answer by Nitesh Rai Wednesday, August 26, 2009 9:59 PM
- Unproposed as answer by Todd McDermid Thursday, August 27, 2009 6:13 PM
Wednesday, August 26, 2009 9:58 PM -
thanks for all your efforts. But, I donno if i communicated wrong.
The column has a width of 5.
If the value is 1, it has to be stored as 00001.
If the value is 003, it has to stored as 00003.
If the value is 312, it has to be stored as 00312.
If the value is 4309, it has to be stored as 04309.
If the value is 00001, it has to be as it is.
NSG12Wednesday, August 26, 2009 10:13 PM -
Try this (I am not sure how efficient is this)
It will be nice to use script component
If
Row.ID.Length = 5 Then
Row.NewId = Row.ID.ToString()
End If
If Row.ID.Length = 4 Then
Row.NewId =
"0" + Row.ID.ToString()
End If
If Row.ID.Length = 3 Then
Row.NewId =
"00" + Row.ID.ToString()
End If
If Row.ID.Length = 2 Then
Row.NewId =
"000" + Row.ID.ToString()
End If
If Row.ID.Length = 1 Then
Row.NewId =
"0000" + Row.ID.ToString()
End If
Nitesh Rai- Please mark the post as answered if it answers your questionWednesday, August 26, 2009 10:27 PM -
- Proposed as answer by Manish Sharma - ETL Thursday, August 27, 2009 5:32 AM
- Marked as answer by Todd McDermid Thursday, August 27, 2009 6:12 PM
Wednesday, August 26, 2009 10:50 PM -
I have another expression which will work:
REPLICATE("0",15 - LEN(Column1)) + Column1
15 is the columns width that u want.
Good to know different ways of doing things.
Hope this helps !! Please close the threads once answered - Sudeep- Proposed as answer by Nitesh Rai Thursday, August 27, 2009 9:28 AM
- Marked as answer by Todd McDermid Thursday, August 27, 2009 6:12 PM
Thursday, August 27, 2009 9:26 AM -
The last 2 answers worked. Thank you all.
NSG12Thursday, August 27, 2009 4:07 PM