How to generate sequence of characters starting from A00001 in sql server-CodePudding

I a writing an sql function which generates a sequence of numbers each time. For example : AA00001, AA00002 till AA99999, then start with AB00001.

I know sequence method in SQL server, but it only accept numbers.

How to achieve this? Can anybody help on this.

Thanks in advance

CodePudding user response：

This has a few assumptions, as my comment got no response.

Firstly, I'm going to assume you are happy with gaps, like an IDENTITY or SEQUENCE. In regards in implementing it like a SEQUENCE or IDENTITY the solution I use here is actually the same as I use a PERSISTED computed column here to define the value. This means you'll need an IDENTITY or SEQUENCE column in your table as well.

This solution is actually pretty simple. For the last 5 characters, you just need the modulo of 100000. For the second character you need to integer division of the value divided by 100000 followed by getting the module of 26, and for the first character it's the value divided by 100000 and then by 26.

This will work for any given value between 0 ('AA00000') and 67599999 ('ZZ99999').

CREATE TABLE dbo.YourTable (I int IDENTITY(1,1) CONSTRAINT chk_I_ValidRange CHECK (I >= 1 AND I <= 67599999),
                            YourSequence AS CONCAT(CHAR((I / 100000 / 26) 65),CHAR(((I / 100000)&) 65),RIGHT(CONCAT('00000',I % 100000),5)) PERSISTED);
GO

INSERT INTO dbo.YourTable
DEFAULT VALUES;
GO
INSERT INTO dbo.YourTable
DEFAULT VALUES;
GO

SET IDENTITY_INSERT dbo.YourTable ON;

INSERT INTO dbo.YourTable(I)
VALUES(7163742),
      (3624),
      (67599999);

SET IDENTITY_INSERT dbo.YourTable OFF;
GO

SELECT *
FROM  dbo.YourTable;
GO

DROP TABLE dbo.YourTable;

db<>fiddle

CodePudding user response：

Extending on @Larnu 's answer, below is an example function that generates an alphanumeric series using a CTE.

CREATE FUNCTION dbo.udf_GenerateSequence(@Count int)
RETURNS TABLE
AS
RETURN (
    WITH 
         t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
        ,numbers AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c CROSS JOIN t10 AS d CROSS JOIN t10 AS e)
    SELECT
        CONCAT(
              CHAR(((num-1) / (99999*26)) % 26   65)
            , CHAR(((num-1) / 99999) % 26   65)
            , RIGHT('0000'   CAST((num-1) % 99999   1 AS varchar(5)), 5)) AS SequenceValue
    FROM numbers
    WHERE
        num <= @Count
        AND num <= 67599324 --avoid rollover
);
GO

In Azure SQL Database and SQL Server 2022 , GENERATE_SERIES may be leveraged to generate the series of numbers, avoiding the need for a CTE to do same.

CREATE FUNCTION dbo.udf_GenerateSequence(@Count int)
RETURNS TABLE
AS
RETURN (
    SELECT
        CONCAT(
              CHAR(((value-1) / (99999*26)) % 26   65)
            , CHAR(((value-1) / 99999) % 26   65)
            , RIGHT('0000'   CAST((value-1) % 99999   1 AS varchar(5)), 5)) AS SequenceValue
    FROM GENERATE_SERIES(1, @Count)
    WHERE
        value <= @Count
        AND value <= 67599324 --avoid rollover
);
GO