January 17, 2008 at 11:12 am
I have a SSIS package in which I need to add leading 0s in a Derived column transformation. Any ideas?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
January 17, 2008 at 11:21 am
You'll have to define it as a string data type (char, varchar, nchar, nvarchar), then make it:
right('0000000000' + col1, 10)
That will make it so it's always 10 digits, with however many leading 0s it needs. For 20 digits, make it 20 zeroes and change the "right" statement to 20 characters.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 17, 2008 at 11:26 am
Then again the value coming through will have different numbers eg. 123 needs to be 0000123 or 4567 needs to be 0004567.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
January 17, 2008 at 11:33 am
That's what GSquared was getting at. Since you want to pad, you want all of the results to be a certain width, even if the non-padded has a variable width. Use his example - it will work.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 17, 2008 at 11:37 am
Using SQL, this demonstrates what GSquared was giving you:
create table dbo.MyData (
IntVal int
)
insert into dbo.MyData (IntVal)
select 123
union all
select 4567
union all
select 987654321
select
IntVal,
right('0000000000' + cast(IntVal as varchar), 10) as CharVal
from
dbo.MyData
drop table dbo.MyData
😎
January 17, 2008 at 11:41 am
alorenzini (1/17/2008)
Then again the value coming through will have different numbers eg. 123 needs to be 0000123 or 4567 needs to be 0004567.
That's what the "right" function is used for. Makes it take the right X number of characters.
So:
'0000000000' + '123' = '0000000000123'
right('0000000000123', 10) = '0000000123'
'0000000000' + '1234567' = '00000000001234567'
right('00000000001234567', 10) = '0001234567'
End result is 10 characters. I just did it all in one step instead of 2 steps. Same end result. Just change the number of 0s in the fixed (first) string, and the number at the end of the "right" statement (just before the close-paren), to fit the number of leading 0s you want.
Keep in mind: If any of your concatenated numbers are more than 10 digits (or whatever number you use in the Right statement), instead of adding 0s, it will remove the leading digits.
'0000000000' + '123456789abc' = '0000000000123456789abc'
right('0000000000123456789abc', 10) = '3456789abc'
Instead of adding 0s, it gets rid of the "12" at the beginning, because the string is 12 characters long.
So, make sure your number of leading 0s >= your maximum number of digits allowed.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 18, 2008 at 7:25 am
When I try to use the following as an expression I get the this error:
right('0000000000' + ConsultantID, 10)
TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [Pad Zeros to ConsultantID [30210]]: Parsing the expression "right('0000000000' + ConsultantID, 10) " failed. The single quotation mark at line number "1", character number "7", was not expected.
Error at Data Flow Task [Pad Zeros to ConsultantID [30210]]: Cannot parse the expression "right('0000000000' + ConsultantID, 10) ". The expression was not valid, or there is an out-of-memory error.
Error at Data Flow Task [Pad Zeros to ConsultantID [30210]]: The expression "right('0000000000' + ConsultantID, 10) " on "input column "ConsultantID" (30304)" is not valid.
Error at Data Flow Task [Pad Zeros to ConsultantID [30210]]: Failed to set property "Expression" on "input column "ConsultantID" (30304)".
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
January 18, 2008 at 8:51 am
Sorry, in SSIS, you have to use regular (double) quotes around strings, not single-quotes. Forgot about that.
I tried this out, and I had to also have a derived column before that to cast the integer as a string, then a second derived column to add the leading zeroes and the "right" function. Two derived column steps.
Once I had those in there, it worked.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 18, 2008 at 9:11 am
Thanks, I put in the double quotes and it worked fine.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply