Get max + 1 without removing zeroes

Question

I have a varchar field that contains numbers in this format "00001" , "00002" etc. When I try to get the next number by using Max(Field) + 1, I get an integer "3" for example.

How can I get the result "00003" instead of "3"?

Here's an working example I've just figure it out, but i think there's must be an easier way:

SELECT TOP (1) 
    { fn REPEAT(0, LEN(ItemId) - LEN(MAX(ItemId) + 1)) } + 
                        CAST(MAX(ItemId) + 1 AS varchar(7)) AS Expr1
FROM Items
GROUP BY ItemId
ORDER BY ItemId DESC

The last query gives the correct result "0004916"

The real test is what should you get for `00009`, `00010` or `000010`? — Sergey Kalinichenko, Sep 14 '16 at 09:40
Possible duplicate of [Formatting Numbers by padding with leading zeros in SQL Server](http://stackoverflow.com/questions/9520661/formatting-numbers-by-padding-with-leading-zeros-in-sql-server) — qxg, Sep 14 '16 at 09:50

score 1 · Answer 1 · answered Sep 14 '16 at 10:30

To do that, you need to convert it to an integer, increment it, and convert it back to a string:

SELECT RIGHT('00000' + CONVERT(VARCHAR, MAX(CONVERT(INT,Field)) + 1), 5) FROM MyTable

But...you're a lot better off having an IDENTITY field which is integer and letting SQL handle the increment, then converting it to a leading-zero string when you need it.One way to do this is to use a Computed column:

CREATE TABLE [dbo].[MyTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Field]  AS (RIGHT('00000'+CONVERT([VARCHAR],[Id],(0)),(5))),
    [Desc] [nchar](10) NOT NULL
) ON [PRIMARY]
GO

That way, the "latest value" will be created for you when you insert a row, and the leading zero sting versionwill be automatically created.

score 0 · Answer 2 · answered Sep 14 '16 at 11:39

Check below example it may help you:

DECLARE @tbl1 AS TABLE
(
    ComputerName VARCHAR(5),
    CodeNumber INT
)

INSERT INTO @tbl1 VALUES('0001',221)
INSERT INTO @tbl1 VALUES('0002',221)
INSERT INTO @tbl1 VALUES('0003',221)

SELECT 
    REPLICATE
    (
        '0',
        4-LEN(MAX(CAST(ComputerName as INT)))
    ) + 
    CAST( MAX(CAST(ComputerName as INT))+1 AS VARCHAR(5)) 
FROM @tbl1
GROUP BY CodeNumber

Get max + 1 without removing zeroes

2 Answers2