Get max + 1 without removing zeroes in SQL server

Question

0.00/5 (No votes)

See more:

Hello,
I have a varchar field 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 resutl "00003" instead of "3"?

Thank you.

What I have tried:

Reply Modify the comment. Delete the comment.
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"

Posted 10-Sep-16 2:18am

Mohamed Sultan

Updated 10-Sep-16 5:14am

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2016-09-10T02:38:00

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

SQL

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:

SQL

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 version will be automatically created.

Garth J Lancaster · Answer 2 · 2016-09-10T02:28:00

Solution 1

you havnt posted any code - so its a bit hard to help ... I'll say this, Im constantly amazed that computer scientists today dont distinguish between

a) how a number is stored in memory
b) how a number is stored in a database
c) how a number may be presented for human consumption - in this case, I mean leading zeros etc

have a look at How to: Pad a Number with Leading Zeros[^]

Posted 10-Sep-16 2:28am

Garth J Lancaster

Updated 10-Sep-16 2:29am

Comments

Mohamed Sultan 10-Sep-16 8:31am

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"

Dave Kreskowiak · Answer 3 · 2016-09-10T05:14:00

The problem with what you're doing is that it CAN and WILL fail in a multiuser environment.

You have to use a SELECT to get the current top value and then an INSERT to put the next number into the table. The problem is what if a second client executes the SELECT before the first client gets to the INSERT? You suddenly have two clients using the same number.

Use auto-incrementing ID fields instead. Leading zeros is more of a user interface thing instead of an ID number in the database.

Get max + 1 without removing zeroes in SQL server

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0