Increment alphanumeric ID to a maximum number

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Increment alphanumeric ID to a maximum number

Author

Topic

MrBloom
Starting Member

36 Posts

Posted - 2014-03-16 : 03:59:15

Hi

I'm Trying to increment an alphanumeric ID column. I have worked out the code below to increment the number part an ID starting
with A0001. However I am using a SQL Server table to store containers in a location grid from 1-12 on the Y axis and A-F on the X axis.
So what I needs to do is when the Incrementing ID reaches A0012 then it should start at B0001 until B0012 and then C0001 and so to F0012
When a new container ID is started and the location reverts back to A0001. I have tried using conditional logic but haven't managed
to work out how to do this yet. Any ideas would be helpful,

Thanks


 declare @val char(5)
  declare @lastval char(5)
   set @lastval = (select max(Location) from [ContainerTable])

   
    if @lastval is null  set @lastval = 'A0001'
           declare @i int 
     set @i = right(@lastval,4) + 1  
      
      SET @val = 'A' + right('000' + convert(varchar(10),@i),4) 
  
  
  INSERT INTO [ContainerTable] (Location)
  VALUES (@val)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-16 : 05:14:35

[code]
select @val = case when convert(int, right(@lastval, 4)) = 12
then char(ascii('A') + (ascii(left(@lastval, 1)) - ascii('A') + 1) % 6) -- cycle A to F
else left(@lastval, 1)
end
+ right('00000' + convert(varchar(4), (convert(int, right(@lastval, 4)) % 12) + 1), 4) -- 1 to 12
[/code]

KH
[spoiler]Time is always against us[/spoiler]

MrBloom
Starting Member

36 Posts

Posted - 2014-03-16 : 07:37:46

Hi Thanks so much for this example it works really well.
The only issue which I have been trying to work on is what to do when the counter reaches F00012 and returns to A0001. This does not increment the second time around and continues to insert A0001 as the @lastval variable is the maximum of the location field, which is always going to be F00012, and so is always going to return to A00001. To solve this I would like to increment another counter, the @ContainerNumber each time the @val counter reaches F00012. Again, I have tried to think how to do this but have not come up with a solution.



  
       declare @val char(5)
    declare @lastval char(5)
     declare @ContainerNumber int
     set @lastval = (select max(Location) from [ContainerTable])
  
      if @lastval is null  set @lastval = 'A0000'
      if @racknumber is null set @racknumber = 0
      
    
       select	@val 	= case 	when 	convert(int, right(@lastval, 4)) = 12
  			then	char(ascii('A') + (ascii(left(@lastval, 1)) - ascii('A') + 1) % 6) 
  			else	left(@lastval, 1)
  			end
  		+ right('00000' + convert(varchar(4), (convert(int, right(@lastval, 4)) % 12) + 1), 4)
 
 
            If @val = 'A0001' set @ContainerNumber = @ContainerNumber +1
            
    INSERT INTO [ContainerTable](ContainerNumber, Location)
    VALUES (@ContainerNumber, @val)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-16 : 09:25:09

set @lastval = (select TOP 1 Location from [ContainerTable] ORDER BY ContainerNumber DESC, Location DESC)

KH
[spoiler]Time is always against us[/spoiler]

MrBloom
Starting Member

36 Posts

Posted - 2014-03-16 : 17:22:05

So simple in the end. thanks so much, that is brilliant logic.
working code below.


    declare @val char(5)
  declare @lastval char(5)
   declare @ContainerNumber int
     set @lastval = (select TOP 1 Location from [ContainerTable] ORDER BY ContainerNumber  DESC, Location DESC)
 set @ContainerNumber = (select max(ContainerNumber) from [ContainerTable])



if @ContainerNumber  is null set @ContainerNumber  = 0 
    if @lastval is null  set @lastval = 'A0000'
    
      
     select	@val 	= case 	when 	convert(int, right(@lastval, 4)) = 12
			then	char(ascii('A') + (ascii(left(@lastval, 1)) - ascii('A') + 1) % 6) 
			else	left(@lastval, 1)
			end
		+ right('00000' + convert(varchar(4), (convert(int, right(@lastval, 4)) % 12) + 1), 4)

 If @val = 'A0001' set @ContainerNumber  = @ContainerNumber  +1
 
  
  
  INSERT INTO [ContainerTable] (ContainerNumber , Location)
  VALUES (@ContainerNumber , @val)

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-03-17 : 09:25:42

If you really need to do this, you will need to increase the isolation for it to work correctly with multiple sessions.
Something like:


WITH LastVals
AS
(
	SELECT TOP 1 
		COALESCE(ContainerNumber, 1) AS ContainerNumber
		,COALESCE(LEFT(Location, 1), 'A') AS LocationLetter
		,COALESCE(CAST(RIGHT(Location, 4) AS smallint), 0) AS LocationNumber
	FROM ContainerTable WITH (UPDLOCK, SERIALIZABLE) -- increase isolation
	ORDER BY ContainerNumber DESC, Location DESC
)
INSERT INTO ContainerTable(ContainerNumber , Location)
SELECT
	CASE
		WHEN LocationLetter = 'F' and LocationNumber = 12
		THEN ContainerNumber + 1
		ELSE ContainerNumber
	END
	,CASE
		WHEN LocationNumber = 12
		THEN CHAR(65 + (ASCII(LocationLetter) - 64) % 6) + '0001'
		ELSE LocationLetter + RIGHT('000' + CAST(LocationNumber + 1 AS varchar(4)), 4) 
	END
FROM LastVals;

You should also make the combination of ContainerNumber and Location unqiue or the primary key.
eg


ALTER TABLE ContainerTable
ADD CONSTRAINT PK_ContainerTable PRIMARY KEY(ContainerNumber, Location);

-- or

CREATE UNIQUE NONCLUSTERED INDEX IN_UN_ContainerTable_ContainerNumber_Location
ON ContainerTable (ContainerNumber, Location);

A check constraint should be added to help enfore the rules.
Something like:


ALTER TABLE ContainerTable
ADD CONSTRAINT CK_ContainerTable_Location CHECK (Location LIKE '[A-F]00[0-1][0-9]');

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources