Very Computer

I have a field that stores reference numbers.  The numbers may be up
to 12 digits long and are sometimes suffixed with a letter.

eg/
        14242
        123345
        4223344
        123343
        122134432A
        123345B

The reference number field therefore has to be of type VARCHAR

My problem is that I need to write a stored proc to return the next
number in a numerically ordered sequence.

ie/
        given an input para of 123343
        the sproc should return 123345

        given 123345
        the sproc should return 123345B

but this obviously sorts on VARCHAR and returns the wrong result

Any ideas???

mat,

you could make two fields out the reference number, one int and one
varchar(1).
since mssql "replace"-function is incapable of regular expressions,
there's only a possibility like

  order by

if only a few distinct letters are used as extensions.
With mssql2k you have at least the possibility to write a user defined
function, that takes only chars with ASCIIcode between 0X30 and 0X39 out
of the input string and returns the result converted to integer. To do
so
write a loop, that uses 'substring'- and 'ASCII'-functions. In the
query string you can use this function as

(don't for get to prefix 'dbo.'!)
good luck

     robert