Subject SQL Question
Author Robert martin

I have a table with a VarChar(20) field. It is commonly used for
storing numbers but (obviously) can also be used to store Chars. I want
to find the highest numeric value in this field (so I can add a new
record with a unique numeric value i.e. max + 1).

I tried

SELECT MAX(CAST(ClientCode AS BigInt)) AS MaxCode
FROM Client
WHERE ClientCode < 'A'

This however fails as 2334324L is less than 'A'. I know there might be
a UDF that does this but we are trying to stick to the standard Firebird
install libraries. Does anybody have a suggestion of how to do it ?

Rob Martin
Software Engineer

phone +64 03 377 0495
fax +64 03 377 0496

Wild Software Ltd