Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

Get next value algorithm


Not sure if anyone can help but I've got a situation where I need to obtain the next available value from a table, from a field which is a varchar(3).

The format of this field is 'NAA' - i.e. first char is a numeric, and the next two chars are alphanumeric. So typical values will look like follows:



Is there any way I can produce a query that will recognise any 'gaps' within the sequences so that I obtain as next value - e.g. getting say '068' or '0AC' from the above?

btw. this field is part of a composite key - thje other value being another varchar(3)

Rgds, Andy
1 Solution

I've come up with an idea, however it may/may not be the most ideal solution. Here are my thoughts.

To detect any gaps in your alphanumeric sequence, I am assuming the following.

The field varchar(3) can contain a range , starting from '000' to '9ZZ'.

What can be done, is to create a single column work table containing all the possible permutations inserted in an ordered sequence. I've provided an example below which inserts all permutations into column 'id' in a work table called tableX.


declare @var1 char(1)
declare @var2 char(1)
declare @var3 char(1)

declare @idx1 int
declare @idx2 int
declare @idx3 int

declare @const varchar(40)

declare @value varchar(3)

select @const = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
select @idx1 = 0
select @idx2 = 1
select @idx3 = 1

select charindex('0',@const)

while(@idx1 < 10)

       while(@idx3 < 37)
          select @value = convert(char(1),@idx1) +    substring(@const,@idx2,1) + substring(@const,@idx3,1)
          insert into tableX values (@value)
          select @idx3 = @idx3 +1
       select @idx2 = @idx2 + 1
       select @idx3 = 1

select @idx1 = @idx1 + 1
select @idx2 = 1


Comparing the work table (tableX) with your existing data table will enable you to obtain all gap values

The query will be something along the lines of

select tableX.id from table X
where not exist
        (select id from YourTable
        where YourTable.id = tableX.id)

YourTable refers to the your data table and id is the column storing the alphanumeric varchar(3)..

The above query returns all the missing values, if you need only the first gap in the sequence each time, you could try 'set rowcount 1' (nasty)..and selecting from tableX, alternatively, the sybase min() function seems to be able to evaluate varchar in a natural sort algorithm, but i'm not 100% sure. The overhead should not be too large, since tableX is relatively small.

Hope this helps.
amtateAuthor Commented:
cheers tenderfoot - I think that this does sound the likey solution...

Rgds, Andy

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now