T-SQL: Get max numeric value in char field having both numbers and letters

Question

In a SQL Server 2008 database table I have a char field that has both numbers and letters. I.e.:

TST
842
UUT
124
674
XTM
763

I need to find the maximum integer in that field. So in the above example I would return "842".

I know how to test for whether the value is numeric or not (ISNUMERIC()function), but I can't figure out how, using that test, to return the maximum integer value.

First of all, do not use `ISNUMERIC` - use `TRY_CONVERT` or `TRY_PARSE` function - http://blogs.msdn.com/b/manub22/archive/2013/12/24/use-new-try-parse-instead-of-isnumeric-sql-server-2012.aspx — gotqn, Jul 28 '15 at 14:15
Thanks, gotqn, but as you can see from the multiple answers below, `ISNUMERIC()` works just fine. (In my particular case, at least. There are always exceptions!) — marky, Jul 28 '15 at 14:21
@gotqn: Since the OP is asking for SQL Server 2008. Is `TRY_CONVERT` or `TRY_PARSE` are available in SQL Server 2008? — Arulkumar, Jul 28 '15 at 14:23
is it possible to have a `.`? if so `isnumeric` may not give you the correct answer if you actually need interger and not just highest number and `TRY_Convert` may truncate the `.` — Daniel E., Jul 28 '15 at 14:23
And there you have your exception! :) I will only have numbers and letters in that field in my table so the `ISNUMERIC()` check works. — marky, Jul 28 '15 at 14:26
Try_Parse converts "1,1" to 11 which is an error for my case: select TRY_PARSE('1,1' as int) — Eralper, Jul 28 '15 at 15:22

score 1 · Accepted Answer · answered Jul 28 '15 at 14:13

1

SELECT MAX(yourcol) FROM T WHERE ISNUMERIC(yourcol)=1 ;

answered Jul 28 '15 at 14:13

Mihai

26,325
7
66
81

score 1 · Answer 2 · answered Jul 28 '15 at 14:14

1

Please try this:

SELECT MAX(field) 
FROM   table 
WHERE ISNUMERIC(field) = 1

answered Jul 28 '15 at 14:14

Roman Marusyk

23,328
24
73
116

score 1 · Answer 3 · answered Jul 28 '15 at 14:14

One way is to use an outer query to get the max from the set of integers:

select MAX(your_char) from (select your_char from table where ISNUMERIC(your_char) = 1) sub

or actually this should work too:

select MAX(your_char) from t where ISNUMERIC(your_char) = 1

T-SQL: Get max numeric value in char field having both numbers and letters

3 Answers3