如何在varchar列中获取数值的MAX值

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

我有一个带有nvarchar列的表。此列具有值，例如：

983
983
294
294
a343
A343
a3546f
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:

我想把这个值的MAX，但不是文本，而是像数字。所以在这个例子中，数字是：

983
983
294
294
343
343
3546
3546

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

MAX值是最后一个--3546。如何在Microsoft SQL上的TSQL中执行此操作？

8 个解决方案

#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:

然后使用RegexReplace（来自该文章），您可以从字符串中提取数字：

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.

然后在SELECT中的MAX（）函数内使用此表达式。

#2

You can try to keep it simple without using Regular Expression 如何在varchar列中获取数值的MAX值

您可以尝试在不使用正则表达式的情况下保持简单

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

#3

CAST() would do the trick, probably.

可能，CAST（）可以做到这一点。

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)

我从这里挑选了它。（虽然我投了reg exp的答案）

#4

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

如果您使用的是SQL Server 2005，或者您也可以使用Sung Meister发布的解决方案

#5

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.

据我所知，您需要创建一个进程（或用户定义的函数）来擦除列，以便您可以实际将其转换为INT或其他适当的数据类型，然后您可以采取最大值。

#6

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

通过使用用户定义的函数将值解析为int，然后运行select。

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

#7

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

and RegexReplace installation like Jason Cohen said

和Jason Cohen这样的RegexReplace安装说

#8

This is an old question, I know - but to add to the knowledge base for others...

这是一个老问题，我知道 - 但要为其他人增加知识库......

Assuming all your values have at least 1 number in them:

假设您的所有值中至少包含1个数字：

Select max(convert(int, SubString(VarName, PATINDEX('%[0-9]%',VarName), Len(VarName))))
from ATable

#1