CODEWITHSUNDEEP
sqlsql-servert-sql
Philip
Philip

Reputation: 2628

Auto Generating Unique Key Not Consistent

I'm trying to create a unique key in the following format:

ABC123456

So for example it would start at ABC000001 and finish at ABC999999, basically first three characters are ABC and then 6 numbers.

I have the following query currently:

  SELECT 
    'ABC' + CAST(REPLICATE('0',6-LEN(RTRIM(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))))+ (SELECT Counter from Counters)) + RTRIM(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + (SELECT Counter from Counters)) as CHAR(9)) as InvoiceNumber,
    ID,
    RandomNumber
 from
    RandomNumbers

Here is some sample data:

CREATE TABLE [dbo].[Counters](
    [Counter] [int] NULL
) ON [PRIMARY]
INSERT INTO [dbo].[Counters]
           ([Counter])
     VALUES
           (0)

CREATE TABLE [dbo].[RandomNumbers](
    [ID] [int] NULL,
    [RandomNumber] [int] NULL
) ON [PRIMARY]
GO

with randowvalues
    as(
       select 1 id, CAST(RAND(CHECKSUM(NEWID()))*100 as int) randomnumber
        union  all
        select id + 1, CAST(RAND(CHECKSUM(NEWID()))*100 as int)  randomnumber
        from randowvalues
        where 
          id < 1000
      )

Insert into RandomNumbers
(
ID,
RandomNumber
)
select *
from randowvalues
OPTION(MAXRECURSION 0)

You'll see it works fine initially, but if you run this for example:

Update Counters
Set Counter = 1

and then re-run the main query, it throws the numbering out. What am I doing wrong here?

Upvotes: 1

Views: 82

Answers (2)

Ilyes
Ilyes

Reputation: 14928

How about this one, it would be better if you have an IDENTITY column there

SELECT STUFF('ABC000000',
             10 - LEN( CAST(RN AS VARCHAR) ),
             LEN(CAST(RN AS VARCHAR)),
             CAST(RN AS VARCHAR)
       ) GenKey,
       SomeCols
FROM
(
   SELECT *,
          ROW_NUMBER() OVER(ORDER BY SomeCols) RN --Simulate IDENTITY column if you have one it would be better
   FROM YourTable 
) T;

Returns:

+-----------+----------+
|  GenKey   | SomeCols |
+-----------+----------+
| ABC000001 | SomeData |
| ABC000002 | SomeData |
| ABC000003 | SomeData |
| ABC000004 | SomeData |
| ABC000005 | SomeData |
| ABC000006 | SomeData |
| ABC000007 | SomeData |
| ABC000008 | SomeData |
| ABC000009 | SomeData |
| ABC000010 | SomeData |
| …         |          |
+-----------+----------+

Demo

If you already have an IDENTITY column, then it would be better and get the right GenKey even some rows deleted from YourTable and even YourTable has more than 999999 rows, here is a Demo simulate this situation.

Upvotes: 1

mohabbati
mohabbati

Reputation: 1158

Using STUFF() function:

with
    cte
as
(
    SELECT 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + (SELECT Counter from Counters) as InvNo,
        ID,
        RandomNumber,
        'ABC000000' as SampleNo
    from
        RandomNumbers
)
select
    stuff(SampleNo, (Len(SampleNo) - Len(InvNo)) + 1, Len(SampleNo), InvNo) as InvoiceNumber,
    ID,
    RandomNumber
from
    cte;

Upvotes: 1

Related Questions