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

20 May 2005 2:52 PM

UGH

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.

20 May 2005 3:19 PM

Armando Prato

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

Show quoteHide quote

"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.
>
>

20 May 2005 3:31 PM

UGH

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.

Show quoteHide quote

"Armando Prato" <apr***@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...
>> 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.
>>
>>
>
>

20 May 2005 3:46 PM

Alejandro Mesa

What is wrong with IsNumeric()?
http://www.aspfaq.com/show.asp?id=2390

AMB

Show quoteHide quote

"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" <apr***@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...
> >> 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.
> >>
> >>
> >
> >
>
>
>

20 May 2005 4:26 PM

UGH

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.

Show quoteHide quote

"Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message
news:E52CDDD8-D5ED-49EA-B6B1-FAA39EC0D07C@microsoft.com...
> 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" <apr***@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...
>> >> 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.
>> >>
>> >>
>> >
>> >
>>
>>
>>