时间戳

首页 > 解决方案 > 从 SQL Server 中的字符串中获取最大的数字？

sql - 从 SQL Server 中的字符串中获取最大的数字？

问题描述

将此问题与其他问题分开：

我想要这样的东西

输入：

Finance

€10m - €50m pretax
<€5m pretax
>€100m
€10m - 50m pretax from 2019

输出：

Number

50
5
100
50

我试过这个

select SUBSTRING([Finance], len([Finance])-CHARINDEX('€',REVERSE([Finance]))+2, 4)

但是 4 并不总是给出正确的长度，并且不适用于€10m - 50m，使用 'm' 作为另一个索引可能是一个解决方案，但太复杂了。还有其他准确而优雅的解决方案吗？

标签： sqlsql-server

解决方案

如果 STRING_SPLIT 函数在您的 Sql Server 版本中不可用？
然后，这里有一个“为应该简单的事情复杂化的方法”方法来获得这些最大数量。

它使用递归 CTE 循环遍历字符串。
然后从中获得 MAX 金额。

示例片段：

-- Sample data
DECLARE @Table table (
  Id int identity(1,1) primary key,
  [Finance] nvarchar(100)
);
INSERT INTO @Table ([Finance]) VALUES 
  ('€10m - €50m pretext')
 ,('<€5m pretext')
 ,('\>€100m')
 ,('€10m - 50m pretax from 2019 ')
 ,('abc €123m def 456m ghi')
;

;WITH RCTE AS
(
   -- The seed query
   SELECT Id, 
    [Finance] AS Str, 
    0 AS Lvl, 
    CAST(NULL AS INT)  AS Num,
    PATINDEX('%[0-9]%', [Finance]) AS pos1,
    PATINDEX('%[0-9][^0-9]%', [Finance]) AS pos2,
   SUBSTRING([Finance],PATINDEX('%[^0-9][0-9]%', [Finance]),1) AS Prefix
   FROM @Table
   WHERE [Finance] LIKE '%[0-9]m%'

   UNION ALL

   -- Looping through the strings
   SELECT Id, 
    SUBSTRING(Str,pos2+1,len(Str)),
    Lvl+1,
    TRY_CAST(SUBSTRING(Str,pos1,pos2-pos1+1) AS INT),
    PATINDEX('%[0-9]%', SUBSTRING(Str,pos2+1,LEN(Str))),
    PATINDEX('%[0-9][^0-9]%', SUBSTRING(Str,pos2+1,LEN(Str))),
    SUBSTRING(Str,pos1-1,1)
    FROM RCTE
    WHERE Str LIKE '%[0-9]m%'
),
AMOUNTS AS
(
    SELECT Id, 
    MAX(Prefix) AS Prefix,
    MAX(Num) AS MaxAmount
    FROM RCTE
    GROUP BY Id
)
SELECT t.Id, a.MaxAmount, a.Prefix, t.[Finance]
FROM @Table t
LEFT JOIN AMOUNTS a ON a.Id = t.Id
ORDER BY t.Id;

结果：

Id  MaxAmount   Prefix  Finance
1   50          €       €10m - €50m pretext
2   5           €       <€5m pretext
3   100         €       \>€100m
4   50          €       €10m - 50m pretax from 2019 
5   456         €       abc €123m def 456m ghi

对 reextester 的测试在这里

但恕我直言，使用 STRING_SPLIT 似乎更好。 

DECLARE @Table table (
  Id int identity(1,1) primary key,
  [Finance] nvarchar(100)
);

INSERT INTO @Table ([Finance]) VALUES 
  ('€10m - €50m pretext')
 ,('<€5m pretext')
 ,('\>€100m')
 ,('€10.0m - 50m pretax from 2019 ')
 ,('abc €123m def 456m ghi')
 ,('200.5m & 50m')


SELECT t.Id, a.Prefix, a.MaxAmount, t.[Finance]
FROM @Table t 
OUTER APPLY
(
  SELECT MAX(LEFT(str,1)) AS Prefix, MAX(TRY_CAST(STUFF(str,1,1,'') AS FLOAT)) AS MaxAmount
  FROM
  (
    SELECT RIGHT(' '+value, PATINDEX('%[^0-9.]%', REVERSE(' '+value))) AS str
    FROM STRING_SPLIT(t.[Finance], 'm') AS spl
    WHERE value LIKE  '%[0-9]'
  ) q
) AS a

对db<>fiddle的测试在这里

推荐阅读