Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select max int from varchar column

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.

like image 722
Chris Van Opstal Avatar asked Feb 04 '09 18:02

Chris Van Opstal


3 Answers

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

like image 105
SQLMenace Avatar answered Oct 21 '22 15:10

SQLMenace


You might try

Select MAX(BoxNumber) from {table} where IsNumeric(BoxNumber) = 1
like image 45
cmsjr Avatar answered Oct 21 '22 15:10

cmsjr


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()

like image 26
fuzz Avatar answered Oct 21 '22 15:10

fuzz