Select max int from varchar column

Question

I am trying to retrieve the largest number from a varchar column that includes both numbers and strings. An example of the data I'm working with:

BoxNumber
123
A5
789
B1

I need to return the largest number (789 in this case) from the column while ignoring the non-numeric values of A5 and B1.

I have found solutions that use custom functions to solve the problem, but I need something that can be executed ad-hoc query without relying on custom functions or procs.

Answer 1

Look into casting the column to an int, then selecting the MAX(). I don't know what it will do to columns that contain letters, but it's worth exploring.

http://doc.ddart.net/mssql/sql70/ca-co_1.htm

Answer 2

You might try

Select MAX(BoxNumber) from {table} where IsNumeric(BoxNumber) = 1

Answer 3

You should check this solution out for values like '+' and '-' as I think the IsNumeric function may return 1 for these values

Answer 4

you need a combination because of the fact that isnumeric returns 1 for the following things

select isnumeric('+'),isnumeric('5d2')

your where clause would be like this

WHERE VALUE NOT LIKE '%[a-z]%'
        AND ISNUMERIC(VALUE) = 1

create table #bla (value varchar(50))
insert #bla values('123')
insert #bla values('a5')
insert #bla values('789')
insert #bla values('b1')

SELECT MAX(CAST(value AS Int)) FROM #bla
WHERE VALUE NOT LIKE '%[a-z]%'
    AND ISNUMERIC(VALUE) = 1

I wrote about this here ISNUMERIC Trouble

Answer 5

The selected answer worked for me until I added this value to the temp table along with the others in the sample:

insert #bla values('1234')

I expected my max() result to now be 1234, but it remained at 789. Perhaps this is due to some collation setting, but I was able to reproduce on multiple databases. I found this query below worked for me, but I'd certainly be interested to hear if there is a more efficient way of doing this. Also, I did not want to include any decimal values, so I excluded anything with a period as well.

SELECT MAX(CAST(Value AS Int)) FROM #bla 
WHERE ISNUMERIC(Value)=1 
    AND Value NOT LIKE '%[a-z]%' 
    AND Value NOT LIKE '%.%'

Answer 6

Why not

SELECT MAX(CAST(Value AS Int)) FROM #bla 
WHERE ISNUMERIC(Value)=1 
    AND Value LIKE '%[0-9]%' 

then you're only dealing with numeric strings. In this case you may not need ISNUMERIC()