How to get MAX value of numeric values in varchar column

Question

I have a table with a nvarchar column. This column has values for example:

• 983
• 294
• a343
• a3546f

and so on.

I would like to take MAX of this values, but not as text but like from numerics. So in this example numerics are:

• 983
• 294
• 343
• 3546

And the MAX value is the last one - 3546. How to do this in TSQL on Microsoft SQL?

Answer 1

First install a regular expression function. This article has code you can cut/paste.

Then with RegexReplace (from that article) you can extract digits from a string:

dbo.RegexReplace( '.*?(\d+).*', myField, '$1' )

Then convert this string to a number:

CAST( dbo.RegexReplace( '.*?(\d+).*', myField, '$1' ) AS INT )

Then use this expression inside a MAX() function in a SELECT.

Answer 2

You can try to keep it simple without using Regular Expression

Here is the source

create table #t ( val varchar(100) )
insert #t select 983
insert #t select 294
insert #t select 'a343'
insert #t select 'a3546f';
GO

;with ValueRange as (
    select  val,
        [from] = patindex('%[0-9]%', val), 
        [to] = case patindex('%[a-z]', val) 
            when 0 then len(val) 
            else patindex('%[a-z]', val) - patindex('%[0-9]%', val) 
               end
    from    #t
)
select  substring(val, [from], [to]) as val
from    ValueRange VR
order by cast(substring(val, [from], [to]) as int) desc