locked
Auto Increment CHAR column RRS feed

  • Question

  • I have a column that I would like to automatically increment and I was wondering if anyone had any suggestions on how to go about it. The column is a CHAR(2) and should consist of any combination of A-Z or 1-9.

    For instance, 11, A1, 1A, and AA are all acceptable entries into the column. I really don't care that the column increments in any order as long as the column remains unique.

    SELECT TOP ( 1 )
    Column_Name + 1
    FROM   dbo.Table_Name
    WHERE  LEN(Column_Name) = ( SELECT    MAX(LEN(Column_Name))
                                                                  FROM      dbo.Table_Name
                                                                )
    ORDER BY Column_Name DESC
    


    The above works great for 1-99 but obviously chokes after that.

    Thanks in advanced.

    Monday, November 7, 2011 7:05 PM

Answers

All replies

  • Found this function which seems to be good from the first glance (haven't tested it):

    http://www.sqlservercentral.com/scripts/Miscellaneous/31448/


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by IT User - GW Tuesday, November 8, 2011 1:31 AM
    Monday, November 7, 2011 7:18 PM
  • You can try something like this...

    USE tempdb
    GO
    
    CREATE TABLE MyTable (ID CHAR(2) PRIMARY KEY)
    
    DECLARE @x TABLE (NumLet CHAR(1))
    
    INSERT INTO @x VALUES
    ('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('0'),
    ('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M'),
    ('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z')
    
    ;WITH CTE AS (
    	SELECT 
    	a.NumLet + b.NumLet AS NumLet
    	FROM @x a, @x b)
    
    INSERT INTO MyTable (ID)
    SELECT TOP 1 NumLet FROM CTE WHERE NumLet NOT IN (
    	SELECT ID FROM MyTable)
    ORDER BY NEWID()
    	
    SELECT * FROM MyTable
    



    Jason Long
    Monday, November 7, 2011 9:16 PM
  • Thanks. This worked very well.
    Tuesday, November 8, 2011 1:32 AM