How to find largest value in a varchar column

Transact-SQL
1

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

2

Check this function out

2 Likes
3

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)

4

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

5

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

6

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

7

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?

8

What was shared with you earlier

TRY_CONVERT (Transact-SQL) - SQL Server

Read it and try it out

9

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.

10

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
11

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

12

What happens if you add

insert into #Temp select 'harrish the great'

insert into #Temp select '1234.00 '

insert into #Temp select ' 234.56'

13

Error Message Yosiasz

Try_Convert which you mentioned

1 Like
14

He can't cause version of sql is old

15

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