max(field) but the field value is not always numeric

I need to find the highest number in one column. Here is a catch, the column
type is varchar and some of the rows will have non number character in it. I
am trying to find the highest number ignoring any row that is not a numeric
value. Here is my query.
SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
Thanks for any input.You may want to cast the column as an int within the max
SELECT max(cast(item_abrv as int)) as mx from item where
isnumeric(item_abrv) = 1
"UGH" <nospam@.noSPam.com> wrote in message
news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
> I need to find the highest number in one column. Here is a catch, the
column
> type is varchar and some of the rows will have non number character in it.
I
> am trying to find the highest number ignoring any row that is not a
numeric
> value. Here is my query.
>
> SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
> Thanks for any input.
>|||I tired that and I got an error. its said converting the varchar value
'100ISBN' to a column of data type int. I just need the query to leave out
any field that has non numeric characters in it.
Thanks.
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:%23V0WT9UXFHA.3228@.TK2MSFTNGP10.phx.gbl...
> You may want to cast the column as an int within the max
>
> SELECT max(cast(item_abrv as int)) as mx from item where
> isnumeric(item_abrv) = 1
> "UGH" <nospam@.noSPam.com> wrote in message
> news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
> column
> I
> numeric
>|||What is wrong with IsNumeric()?
http://www.aspfaq.com/show.asp?id=2390
AMB
"UGH" wrote:

> I tired that and I got an error. its said converting the varchar value
> '100ISBN' to a column of data type int. I just need the query to leave out
> any field that has non numeric characters in it.
> Thanks.
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:%23V0WT9UXFHA.3228@.TK2MSFTNGP10.phx.gbl...
>
>|||That was it and I modified my query to do this.
SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1 and
item_abrv not like '%e%' and item_abrv not like '%d%'
Thanks for your help.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E52CDDD8-D5ED-49EA-B6B1-FAA39EC0D07C@.microsoft.com...[vbcol=seagreen]
> What is wrong with IsNumeric()?
> http://www.aspfaq.com/show.asp?id=2390
>
> AMB
> "UGH" wrote:
>