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

As far as I know you would need to create a process (or user defined function) to scrub the column, so that you can actually convert it to an INT or other appropriate datatype, then you can take the max of that.

Answer 2

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)

Answer 3

By using user defined function parse the value to an int and then run the select.

SELECT MAX(dbo.parseVarcharToInt(column)) FROM table

Answer 4

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 5

you can write a function something like

create FUNCTION [dbo].[getFirstNumeric](
    @s VARCHAR(50)
)  
RETURNS int AS 
BEGIN

set @s = substring(@s,patindex('%[0-9]%',@s),len(@s)-patindex('%[0-9]%',@s) + 1) 
if patindex('%[^0-9]%',@s) = 0
    return @s
set @s = substring(@s,1,patindex('%[^0-9]%',@s)-1) 

return cast(@s as int)
end

and then call

select max(dbo.getFirstNumeric(yourColumn)) from yourTable

if you are using SQL Server 2005 or never you can also use the solution posted by Sung Meister

Answer 6

SELECT  dbo.RegexReplace('[^0-9]', '','a5453b',1, 1)

and RegexReplace installation like Jason Cohen said