May 14, 2009 at 9:20 am
Alvin Ramard (5/14/2009)
Please try this and tell me what you get.
declare @CustomerRef as varchar(50)
set @CustomerRef = '00000012345678900'
select REPLACE(LTRIM(REPLACE(@CustomerRef,'0',' ')), ' ','0')
That won't work, Alvin. Rich is in the SSIS Expression editor.
[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]
May 14, 2009 at 9:47 am
Ramesh
I suspect there's something missing here [Wink]
Are you sure that you wanted to replace all zeros with empty strings or just the leading zeros?
I'm having a bad day today:hehe:, most of things seems to go wrong:hehe: today
Barry
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?
I've just checked the expression
REPLACE( LTRIM( REPLACE( "0000000000003345", "0", " " ) ), " ", "0" )
and it gives me the desired results.
--Ramesh
May 14, 2009 at 11:07 am
Great!
[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]
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply