Get the biggest number from a string in SQL Server?

Question

To part this question from other questions:

• The numbers are in every string
• There are sometimes more than one number in a string
• I only need the biggest one, representing money, not year
• Euro sign doesn't appear in front of every number
• 'pretax' doesn't appear in every row
• I don't have the right to create function

I want to have something like this

Input:

Finance

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

Output:

Number

50
5
100
50

I have tried this

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

but 4 doesn't always give the correct length, and doesn't work for €10m - 50m, using 'm' as another index could be a solution, but too complex. Is there any other solution which is accurate and elegant?

Answer 1

SQL Server is not optimal for string parsing. But you seem to have a very limited set of substrings you are looking for.

• They end with 'm'.
• They have '<', '>', and '€'.
• They are separated by spaces.

This suggests splitting the string and doing some more basic processing on each piece. For your sample data, this works:

with t as (
      select v.*
      from (values ('€10m - €50m pretax'), ('<€5m pretax'), ('>€100m')) v(str)
     )
select *
from t outer apply
     (select top (1) try_convert(int, replace(replace(replace(replace(s.value, '€', ''), 'm', ''), '<', ''), '>', '')) as num
      from string_split(t.str, ' ') s
      where value like  '%m'
      order by try_convert(int, replace(replace(replace(replace(s.value, '€', ''), 'm', ''), '<', ''), '>', '')) desc
     ) x;

Here is a db<>fiddle.

Answer 2

If the STRING_SPLIT function isn't available in your Sql Server version?
Then here is a "Way To Complicated For Something That Should Be Simple" method to get those max amounts.

It uses a recursive CTE to loop through the strings.
Then gets the MAX amounts from that.

Example snippet:

-- 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;

Result:

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

A test on rextester here

But i.m.h.o. using STRING_SPLIT seems nicer.

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

A test on db<>fiddle here