derived column trimming leading zeros

Question

derived column trimming leading zeros

Rich96

SSC Eights!

Points: 947
More actions
May 13, 2009 at 2:39 pm

#130094

Something about this derived columns expression is not working:
REPLACE( LTRIM( REPLACE( [Account] , '0', '' ) ) , '','0')
It's probably something simple but I don't work with SSIS that often. Any ideas would be greatly appreciated.
Data:
0000000001234556
0000000023455668
0000000000003345
Desired:
1234556
23455668
3345

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

That will remove all of the zero characters from [Account] if account is a string. Is that what you wanted to do?

If your intent was to change all of the zeroes to spaces, trim the left side and then change the embedded spaces back to zero, your expression will not do that because your ('') is an empty string and not a space. Try changing both ('')'s to a space (' ').

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 2

There should be a space between each pair of single quotes. The space is missing from the logic you pasted.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Rich96 (5/13/2009)
Something about this derived columns expression is not working:
REPLACE( LTRIM( REPLACE( [Account] , '0', '' ) ) , '','0')
It's probably something simple but I don't work with SSIS that often. Any ideas would be greatly appreciated.
Data:
0000000001234556
0000000023455668
0000000000003345
Desired:
1234556
23455668
3345

If the account numbers are always digits only, just cast the account number as a bigint and you're done. If you need it as a varchar, then cast the resulting bigint as a varchar.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rich96 SSC Eights! Points: 947 More actions · Answer 4

I probably didn't make myself clear. I'm using the Derived Column Transformation Editor.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Rich96 (5/13/2009)
I probably didn't make myself clear. I'm using the Derived Column Transformation Editor.

Actually I had assumed that since this is the SSIS forum. In any event, that doesn't affect any of the replies made so far.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Rich96 SSC Eights! Points: 947 More actions · Answer 6

RBarryYoung (5/13/2009)
That will remove all of the zero characters from [Account] if account is a string. Is that what you wanted to do?
If your intent was to change all of the zeroes to spaces, trim the left side and then change the embedded spaces back to zero, your expression will not do that because your ('') is an empty string and not a space. Try changing both ('')'s to a space (' ').

...of course, correct forum. Thanks for the clarification.

I do want to remove the zero characters from the string. I added the spaces with the same results. I can use this expression in a select statement and it works just fine. I'll dig into the BOL.

Mukti SSCrazy Points: 2826 More actions · Answer 7

you need to use double quotes instead of single in your expression. Try:

REPLACE( [Account] , "0", "" ) and it should get rid of all the zeros.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Rich96 (5/13/2009)
RBarryYoung (5/13/2009)
That will remove all of the zero characters from [Account] if account is a string. Is that what you wanted to do?
If your intent was to change all of the zeroes to spaces, trim the left side and then change the embedded spaces back to zero, your expression will not do that because your ('') is an empty string and not a space. Try changing both ('')'s to a space (' ').
...of course, correct forum. Thanks for the clarification.
I do want to remove the zero characters from the string. I added the spaces with the same results. I can use this expression in a select statement and it works just fine. I'll dig into the BOL.

Oh right. I didn't check if the function names were correct and I always forget that SSIS's functions aren't entirely the same as T-SQL's

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Rich96 SSC Eights! Points: 947 More actions · Answer 9

RBarryYoung (5/13/2009)
Rich96 (5/13/2009)
RBarryYoung (5/13/2009)
That will remove all of the zero characters from [Account] if account is a string. Is that what you wanted to do?
If your intent was to change all of the zeroes to spaces, trim the left side and then change the embedded spaces back to zero, your expression will not do that because your ('') is an empty string and not a space. Try changing both ('')'s to a space (' ').
...of course, correct forum. Thanks for the clarification.
I do want to remove the zero characters from the string. I added the spaces with the same results. I can use this expression in a select statement and it works just fine. I'll dig into the BOL.
Oh right. I didn't check if the function names were correct and I always forget that SSIS's functions aren't entirely the same as T-SQL's

I think I'm just going to burn this vendor's file and go watch Star Trek. 🙂 I went ahead and added an 'Execute SQL Task' in my dataflow to UPDATE the specific column. I also found this to help me with the available functions in SISS:

http://technet.microsoft.com/en-us/library/ms141671(SQL.90).aspx

Thanks for the assistance.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

Rich96 (5/13/2009)
I probably didn't make myself clear. I'm using the Derived Column Transformation Editor.

Sorry... I didn't believe it would make a difference. I don't actually use SSIS for anything. The cool part is, you just gave me another reason not to. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 11

I suspect there's something missing here;-)

Are you sure that you wanted to replace all zeros with empty strings or just the leading zeros?

--Ramesh

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 12

Alvin Ramard (5/13/2009)
There should be a space between each pair of single quotes. The space is missing from the logic you pasted.

Add the missing space between the single quotes and that will work. It will NOT replace the zeroes in the middle of the number.

I've used that myself.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

Actually, folks, I believe that Rich already said that he tried using a single space and it didn't work.

If I am reading the screen shot correctly, I think that it is saying that the syntax is bad, which makes me think that Mukti's comment below is the most relevant one:

Mukti (5/13/2009)
you need to use double quotes instead of single in your expression. Try:
REPLACE( [Account] , "0", "" ) and it should get rid of all the zeros.

So Rich, have you tried this yet?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 14

Please try this and tell me what you get.

declare @CustomerRef as varchar(50)

set @CustomerRef = '00000012345678900'

select REPLACE(LTRIM(REPLACE(@CustomerRef,'0',' ')), ' ','0')

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]