How to get MAX value of numeric values in varchar column

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.

CAST() would do the trick, probably.

SELECT MAX(CAST(yourColumn AS int)) AS maxColumns FROM yourTable

Edit. I didn't read the whole question, as it seems...

– Function to strip out non-numeric chars
ALTER FUNCTION dbo.UDF_ParseNumericChars
(
  @string VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
  DECLARE @IncorrectCharLoc SMALLINT
  –SET @IncorrectCharLoc = PATINDEX(’%[^0-9A-Za-z]%’, @string)
  SET @IncorrectCharLoc = PATINDEX(’%[^0-9.]%’, @string)
  WHILE @IncorrectCharLoc > 0
  BEGIN
    SET @string = STUFF(@string, @IncorrectCharLoc, 1, ”)
    SET @IncorrectCharLoc = PATINDEX(’%[^0-9.]%’, @string)
  END
  SET @string = @string
  RETURN @string
END
GO

I picked it from here. (I voted up the reg exp answer though)

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