CODEWITHSUNDEEP
sqlsql-server
Ryan Hargreaves
Ryan Hargreaves

Reputation: 277

SQL Server - Enforcing the number of digits on an int

I have a unique ID that I am generating program-side in the format CCYYMMDDxxxx, where xxxx is a 4 digit string that will auto increment, starting from 0001.

To calculate the next element, I have wrote part of a query which gets those 4 digits from the string using substring.

DECLARE @number int, @nextstring varchar(4)
SET @number = (SELECT CONVERT(int, SUBSTRING(Payment_ID, 9, 4), 103) FROM Orders)

I need to be able to increment it by 1, but keep it in 4 digit format. I came across the 'right' keyword, but I don't know how many 0's ill need to put in front of it.

Is there a nice way to do this without a bunch of IF's? Of course, I could calculate the length and put the respective number of 0's at the start, but that doesn't account for 9, 99, and 999.

Upvotes: 0

Views: 850

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

I really think that an identity column is the best way to handle this . . . then assign the sequential number afterwards.

But, if you want to do this, you need to left pad the number. Here is a method to get the next id based on values in the table:

SELECT (LEFT(MAX(payment_id), 8) +
        RIGHT('0000' + CAST(CAST(RIGHT(MAX(payment_id), 4) as int) + 1 as VARCHAR(255))
       )
FROM Orders;

This does not verify that the id is long enough. Let me repeat: I think it is much better to use an identity column as the id and then construct whatever attributes you want (such as the number within a day) when you need that information.

Upvotes: 2

Related Questions