Get next value algorithm

Posted on 2003-03-13
Medium Priority
Last Modified: 2012-06-22

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
Question by:amtate
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

tenderfoot earned 1500 total points
ID: 8151528

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.

Author Comment

ID: 8151570
cheers tenderfoot - I think that this does sound the likey solution...

Rgds, Andy

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We are witnesses that everyone is saying that our children shouldn't "play" with a technology because it is dangerous. This article is going to prove that they are wrong.
What's worse than having your data encrypted by ransomware? Getting attacked by a so-called "wiper," which simply destroys the data and offers you no hope of ever seeing it again.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month14 days, 9 hours left to enroll

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question