SQLTeam.com | Weblogs | Forums

How to find largest value in a varchar column

How can I find the largest numeric value in a column that is defined as varchar? The column contains rows which have a numeric value and others which have character values.
Thanks

Check this function out

2 Likes

I'll look into that function, @yosiasz.
But why doesn't this return the largest number?

SELECT MAX(CheckNbr)
FROM PaymentDetail
WHERE ISNUMERIC(CheckNbr) = 1

It returns 9999 but there are thousands of rows having a larger checknbr value. This col is varchar(50)

What are you trying to find?

Try this

with cte
As
(
SELECT CheckNbr
FROM PaymentDetail
WHERE ISNUMERIC(CheckNbr) = 1
)
Select top 1 CheckNbr
From src
Order by 1 desc

I updated statistics on this table. That didn't help.

Your code also returns 9999 as the largest CheckNbr.

Oh I need to tell the staff the next CheckNbr to use for the next run of AP checks.

What the heck is wrong?
Thanks

Probably means you have junk data in that column. Spaces and what not

Yes the column allows for nulls too. Some check numbers have spaces at the end. So how can I find all check numbers that contain both numeric and non-numeric values?

What was shared with you earlier

TRY_CONVERT (Transact-SQL) - SQL Server

Read it and try it out

I would need to run it like this I suppose:
SELECT TRY_CONVERT(int, PaymentDetail.CheckNbr) AS Result;

But I get TRY_CONVERT is not a valid function - because even though this instance is 2008, the database is at 2005 Comp Level. I can't change that so I'm stuck.

This seems to be what I need:

SELECT * FROM PaymentDetail WHERE CheckNbr not like '%[^0-9]%' and CheckNbr != '' AND CreateDT < 'September 23, 2018'

It returns only truly numeric Check numbers.
Thanks for your tips.

1 Like

hi hope this helps

create sample data

create table #Temp ( Number varchar(50) )

insert into #Temp select '1234.56'
insert into #Temp select '34567890'
insert into #Temp select '345'
insert into #Temp select '2234.56'
insert into #Temp select '127890'

select 
     top 1 * 
from 
    #Temp 
order by 
   cast(Number as numeric) desc

image

What happens if you add

insert into #Temp select 'harrish the great'

insert into #Temp select '1234.00 '

insert into #Temp select ' 234.56'

Error Message Yosiasz

Try_Convert which you mentioned

He can't cause version of sql is old

All: This meets my needs just fine: Thanks

SELECT * FROM PaymentDetail WHERE CheckNbr not like '%[^0-9]%' and CheckNbr != '' AND CreateDT < 'September 23, 2018'
Order by CheckNbr Desc

Ideally, I would like to just return the largest single value but I can use this as a workaround.

It returns only truly numeric Check numbers. I just have to paste them into Excel, then sort.
Thanks for your tips.

1 Like