SQL for getting MAX number from varchar field

Question

0.00/5 (No votes)

See more:

I have a table where has a field "billNo" as varchar. Table has two kinds of data. One kind is non number and other kind is number type. Like the following-

Table name: billinfo
Field and data:

VB

I need a sql what return max number from the field "billNo" from the table billinfo. Remember- The non number value no need to consider. I need the return value is 08.

Please ..

Posted 8-Apr-14 2:28am

N H Faruque

Add a Solution

Comments

CHill60 8-Apr-14 8:37am

'08' would not be the maximum number from your sample - are you saying that the max should ignore anything like '1004-0060' etc?

5 solutions

Solution 2

You can't really do that: SQL doesn't have a TryParse method which lets you attempt to convert a string to a number and continue if it fails.
And you can't use MAX on a string field and get numeric results unless the strings are all the same length and padded with leading zeros, as a string comparison stops at the first character difference and uses that as the basis for the whole comparison.

The best solution is to use two different separate fields for this: one sting based, and one numeric. There is no "nice" solution with mixed data such as you show.

Posted 8-Apr-14 2:40am

OriginalGriff

Comments

Maciej Los 8-Apr-14 10:02am

Really?
TRY_PARSE[^]

OriginalGriff 8-Apr-14 10:48am

Really: TRY_PARSE is SQL 2012 onward only.
It doesn't exist in "most" SQL installations...

Maciej Los 8-Apr-14 12:00pm

I know it ;)

Maciej Los 8-Apr-14 10:09am

Useful solution.
A 4, because of above comment.
Have a look at my answer ;)

Solution 4

If you can find a way of ordering the billno you could try

SQL

SELECT TOP 1 billNo from billinfo ORDER BY billNo DESC

but as I mentioned in my comment above you might want to exclude the other "style" of entry with

SQL

WHERE billNo NOT LIKE '-'

Either way you really need to reconsider your table schema

Posted 8-Apr-14 3:19am

CHill60

Solution 5

OriginalGriff suggested the idea. Thanks, Paul!

Have a look at TRY_PARSE[^] method!
Note: This method is available starting from MS SQL Server 2012!
As you can see, it possible to get numeric format depends on local culture.

Posted 8-Apr-14 4:08am

Maciej Los

Updated 8-Apr-14 4:12am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Er. Puneet Goel · Accepted Answer · 2014-04-08T02:38:00

Solution 1

Try This :
temp is table name and number is column name

SQL

select max(Convert(int, number)) from temp where isnumeric(number) = 1

Posted 8-Apr-14 2:38am

Er. Puneet Goel

N Haque · Accepted Answer · 2014-04-08T02:42:00

Solution 3

try this-

SQL

Select MAX( CAST(billNo as int)) from
billInfo
Where billNo NOT LIKE '%-%'

or

SQL

Select MAX(numberBillNo)
from (
SELECT Cast(billNo as int) as numberBillNo  FROM billInfo
Where billNo NOT LIKE '%-%'
) as tmp

Posted 8-Apr-14 2:42am

N Haque