How can I get the greatest string value that can be converted to an int from SQL server?

Question

I have a table in MS SQL Server 2008 with a varchar column called WONO. For most records, the value of this field is a padded-on-the-left string representation of an integer, such as:

'   384564'

However in some cases there is a non-integer value in the field. I want to select the record with the greatest integer in the WONO column, ignoring those that don't have an integer there. What's the select statement for this?

Answer 1

SELECT MAX(cast(WONO as integer)) AS WONO FROM
(
    SELECT WONO 
     FROM YourTable
    WHERE ISNUMERIC(WONO) = 1
) SubTable

Keep in mind this may act weird when you have stuff like 1E1 or 2D2 because of how ISNUMERIC handles certain strings (1E1 and 2D2 are actually considered exponents by ISNUMERIC). If that becomes an issue, you can look at this custom function, IsReallyNumeric.