getting max number from a char column

[CSdjohnson]

i have a table with a string column called custnum. the customer number can be alphanumeric. if i have customer numbers 1,2,3,4,5,6,7,8,9,10 and i run the following query:

select max(custnum) as newnum
from dbo_customers
where type_cd = "C"
and isnumeric(custnum);

then i get a data record with custnum = 9 rather then 10.

how can i get the correct custnum of 10 to be returned without having to change the column to type integer?

 

[TonyJollans]

Hi CSdjohnson,

It's because you are doing a STRING comparison. if you want a numeric comparison, you must use NUMBERS. You could use ..

[blue]select max([highlight]Val([/highlight]custnum[highlight])[/highlight]) as newnum
from dbo_customers
where type_cd = "C"
and isnumeric(custnum);[/blue]

Val takes as much as it can from the beginning of a string and converts it to a number. This will work for the sample data you give but if you also had a customer number of, say, "11A", it would return 11, so it very much depends on ypur data.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.