Hi All:

Here is my question:

I have a table with a field called REFNBR, this is defined as CHAR(10).
The current value in the field is '0000000000'.

I would like to update this field to '000000001', so I want to have a
stored procedure or an update statement that will increment this value
by 1 everytime.

When I run:

UPDATE MyTable
Set
REFNBR = REFNBR + 1

It places the value of '1' in the database, i would like '0000000001',
and '0000000002' and so on..

any help is appreciated!!

Hi,
I wrote a quick left pad function that can be used for this. It
basically pads the target with the pad char so that it will left pad
the string until it hits the desired end length.

UPDATE MyTable
Set
REFNBR = dbo.PadLeft(REFNBR + 1, '0', 10)


The function create stmt:

CREATE FUNCTION [dbo].[PadLeft] (@Target varchar(50), @PadChar char(1),
@EndLength smallint)
RETURNS varchar(50)

AS
BEGIN
IF @EndLength > 50
BEGIN
RETURN @Target
END

WHILE LEN(@Target) < @EndLength
SET @Target = @PadChar + @Target
RETURN @Target
END
GO