how to concatenate zeros before the col value

[shalini11]

Hi All,

The requirement is that I have to select the max of column value from a table and the add 1 to it and insert the new record.

The column is varchar(5). So if the number after adding 1 is less than 5 digits then, zeros should be added before the number to make the field of 5 digits.

For ex:

The input field has values:
00000
00001
00002
00003

Now the max will return 00003.
After adding 1 it becomes 4 and gets inserted with value 4. But it should be inserted as 00004.
I tried using len function and padchar also but not able to do it.

Could you pls help

Thanks

[shalini11]

hey, I got the solution.

I used the below derivation:

If Len(AspCodeCountSVar) ='0' Then AspCodeCountSVar Else Str('0',5-Len(AspCodeCountSVar)):AspCodeCountSVar

where AspCodeCountSVar contains the value after adding 1.

[ShaneMuir]

If Len(AspCodeCountSVar) ='0' Then AspCodeCountSVar Else Str('0',5-Len(AspCodeCountSVar)):AspCodeCountSVar

You could have also used something like
Right('00000':AspCodeCountSVar,5)

[OddJob]

You could have also used something like
Right('00000':AspCodeCountSVar,5)

You could also try writing a parallel routine to do this - nice opportunity to learn C++!

[asorrell]

In the testing I've done, I use Shane's method (slap extra zeroes on the front and truncate to length) because it is signficantly faster than any other method I've found. It is basically two operations, pad and truncate with no logical tests to perform.