Next Logical Integer from a VARCHAR field

Next Logical Integer from a VARCHAR field

Post by MAT » Thu, 30 Aug 2001 01:15:03

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


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.

        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???


Next Logical Integer from a VARCHAR field

Post by Robert Lummer » Thu, 30 Aug 2001 19:01:06


you could make two fields out the reference number, one int and one
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
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



1. Getting MAX integer value from varchar field

I have to return the maximum integer value from a column that can contain
both text and numbers.  For example, given the values:

the T-SQL statement / stored procedure would return 693.  Anyone have any
ideas on how to do this?
Neil Negandhi

Senior Consultant, BDO Dunwoody

2. How do I add html formatting tags to memo?

3. Next free integer value

4. Borland Database Engine & Informix 7.1 problem

5. SQL question - next available integer

6. Joined a domain, now login failure

7. plb returns logical instead of integer...

8. Informix DBA Required

9. Waiting for Next Logical Log File to be Freed

10. sp_cursorfetch much slower on varchar index than integer?

11. converting varchar to integer

12. Appending varchar and integer data causes performance loss

13. Varchar/Integer Conversion error