Padding zero's to a variable in a SQL Stored Procedure that updates?

[Imagination]

Hi,

SQL Question.

I have a stored procedure that is called by DCOM. The COM sends over @String and I need to chop the string up into two variables and then update a table. The value of @cde can be 1,23, 143 and so on but I need to pad it with zero's if its ever one or two digits long before updating the table. Based on what i'm doing below, can someone tell me how to pad zero's to @cde before the update?

set @cr_tax = ltrim(rtrim(substring(@String, 1 , patindex("%`~%", @String)-1)))
set @String = Case When patindex("%`~%", @String)>0 Then substring(@String, 2 + patindex("%`~%", @String), 2000) Else '' End
set @cde = ltrim(rtrim(substring(@String, 1 , patindex("%`~%", @String)-1)))
set @String = Case When patindex("%`~%", @String)>0 Then substring(@String, 2 + patindex("%`~%", @String), 2000) Else '' End

Update table1 set cr_tax = @cr_tax and cde = @cde

Thanks!

 

[leeland]

This might be what your looking for....works in SQL2000

 

[brandonb]

So the field is 3 characters long which need to be padded with 0's

SET @cde = RIGHT("000" + @cde, 3)

Thats how I've done it for the last few years at 2 different companies.

 

[leeland]

Originally posted by: brandonb
So the field is 3 characters long which need to be padded with 0's

SET @cde = RIGHT("000" + @cde, 3)

Thats how I've done it for the last few years at 2 different companies.

by using the replicate command you can set it to be dynamic. So the field length can be anything with the parameters you set and it will automatically adjust it.

 

[Evadman]

T-sql to make sure always padded to 6 characters with leading zeros.

while len(@cde) < 6
set @cde = '0' + @cde