Zero fill question

[Palmyra]

I have data values = 'D5000-5'; sometimes D5000-05.

I need to update them to be zero filled on the right side of the dash, so that I always have five digits on the right side of the dash, e.g., D5000-00005.

Can I do that in a right function?

 

[djj55]

You will need to split the value then pad using one of many techniques. I prefer the right('00000' + value, 5).

To do the split you could use the LEFT, CHARINDEX, and SUBSTRING functions.

Good Luck,

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[bborissov]

[COLOR=blue]DECLARE[/color] @Test [COLOR=blue]varchar[/color](200)
[COLOR=blue]SET[/color] @Test = [COLOR=red]'D5000-05'[/color]

[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]LEFT[/color](@Test, [COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]'-'[/color], @Test))+
       [COLOR=#FF00FF]RIGHT[/color]([COLOR=red]'00000'[/color]+[COLOR=#FF00FF]SUBSTRING[/color](@Test, [COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]'-'[/color], @Test)+1, 8000),5)
       
[COLOR=blue]SET[/color] @Test = [COLOR=red]'D5000-5'[/color]
[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]LEFT[/color](@Test, [COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]'-'[/color], @Test))+
       [COLOR=#FF00FF]RIGHT[/color]([COLOR=red]'00000'[/color]+[COLOR=#FF00FF]SUBSTRING[/color](@Test, [COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]'-'[/color], @Test)+1, 8000),5)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.