CODEWITHSUNDEEP
sql-server
ca8msm
ca8msm

Reputation: 1210

Create a custom sequence with letter prefix

I have a sequence in SQL Server

CREATE SEQUENCE dbo.NextBusinessValue
START WITH 1  
INCREMENT BY 1 ;  
GO

And I'd like to use this to generate a 5 digit custom reference number that uses this sequence to create the number in the format A0000.

The rules for the reference number are that:

1-9999 would be A0001 - A9999

10000-19999 would be B0000 - B9999

20000-29999 would be C0000 - C9999 etc...

It won't ever get the the amount of data that requires going past Z.

I know I can get a letter by using:

SELECT CHAR(65)

So this would work for 1-9999:

declare @n int = 9999
SELECT CHAR(65) + right('0000' + convert(varchar(10), @n), 4)

But would fail when it reaches 10000.

What methods can be used to increment the letter each time the sequence hits the next block of 10000?

Upvotes: 2

Views: 3168

Answers (4)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67301

(edited)
You should not use this as primary key, but rather calculate your format for the output on-the-fly. For a faster search I'd reccomend to use the following to calculate a persistant computed column, which you can use with an index.

DECLARE @mockingTbl TABLE(SomeSeqValue INT);
INSERT INTO @mockingTbl VALUES(0),(1),(999),(1000),(9999),(10000),(12345),(50000);

SELECT A.NumeralPart
      ,B.Rest
      ,C.StartLetter
      ,C.StartLetter+REPLACE(STR(A.NumeralPart,4),' ','0') AS YourCode
FROM @mockingTbl AS m
CROSS APPLY(SELECT m.SomeSeqValue % 10000 AS NumeralPart) AS A
CROSS APPLY(SELECT (m.SomeSeqValue-A.NumeralPart)/1000 AS Rest) AS B
CROSS APPLY(SELECT CHAR(B.Rest + ASCII('A'))) AS C(StartLetter)

Upvotes: 1

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131334

UPDATE AND WARNING

Having a primary key and a business key used for display, invoicing is very common. The business key has to be stored and indexed because business users will use it to search for records, documents etc. You shouldn't use the business key as the primary key though.

ORIGINAL

You already get the first digit with @n/10000. Add that to 65 to get the first letter.

To get the remainder you can perform a modulo operation, @n/10000 and format the result as a string:

select char(65 + @n/10000) + format(@n % 10000 ,'d')

Sequences and FORMAT were both introduced in SQL Server 2012, so you can be assured that FORMAT is always available.

9999 will return A9999, 19999 will return B9999 etc.

The scale can be a parameter itself

select char(65 + @n/@scale) + format(@n % @scale ,'d')

Upvotes: 4

Richard Hansell
Richard Hansell

Reputation: 5403

Something like this?

DECLARE @n INT = 9999;
WHILE @n < 26000
BEGIN
    SELECT CHAR(65 + CONVERT(INT, @n / 10000)) + RIGHT('0000' + CONVERT(VARCHAR(10), @n), 4);
    SELECT @n = @n + 1;
END;

Upvotes: 1

Steve Ford
Steve Ford

Reputation: 7753

Personally I would handle this either in your display code or add it as a computed field either ti the table or view.

This would work upto Z:

declare @n int = 9999

-- Gives A9999
SELECT CHAR(@n / 10000 + 65 ) + right('0000' + convert(varchar(10), @n), 4)

SET @n = 10000
-- Gives B0000
SELECT CHAR(@n / 10000 + 65 ) + right('0000' + convert(varchar(10), @n), 4)

SET @n = 10001
-- Gives B0001
SELECT CHAR(@n / 10000 + 65 ) + right('0000' + convert(varchar(10), @n), 4)


SET @n = 20001
-- Gives C0001
SELECT CHAR(@n / 10000 + 65 ) + right('0000' + convert(varchar(10), @n), 4)


SET @n = 200001
-- Gives U0001
SELECT CHAR(@n / 10000 + 65 ) + right('0000' + convert(varchar(10), @n), 4)


SET @n = 300001
-- Gives _0001
SELECT CHAR(@n / 10000 + 65 ) + right('0000' + convert(varchar(10), @n), 4)

Upvotes: 1

Related Questions