Get next value algorithm

Hi,

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:

'067'
'069'
'090'
'0AA'
'0AB'

etc.

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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Andy,

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.

eg.

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)
begin
while(@idx2<37)
begin

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

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

end

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.
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
cheers tenderfoot - I think that this does sound the likey solution...

Rgds, Andy
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.