Extract multiple decimal numbers from string in T-SQL

Question

I have a table in SQL Server Management Studio with columns containing ranges of numbers as strings. I am trying to find a way to extract the numeric values from the string and insert them into a new table.

For example, in the table I have the value 12.45% - 42.32% as a string. I'd like to be able to get 12.45 and 42.32 and insert them into a new table with columns min_percent and max_percent.

I found several ways to extract a single numeric value from a string using SQL, and also tried modifying the function from Extract numbers from a text in SQL Server (which returns multiple integers, but not decimals), but so far I haven't been able to get it working. Thanks in advance for any suggestions

score 1 · Answer 1 · answered Jun 24 '13 at 18:44

The function is quite close. You just use numeric and add the point:

  with C as
  (
    select cast(substring(S.Value, S1.Pos, S2.L) as decimal(16,2)) as Number,
           stuff(s.Value, 1, S1.Pos + S2.L, '') as Value
    from (select @String+' ') as S(Value)
      cross apply (select patindex('%[0-9,.]%', S.Value)) as S1(Pos)
      cross apply (select patindex('%[^0-9,.]%', stuff(S.Value, 1, S1.Pos, ''))) as S2(L)
    union all
    select cast(substring(S.Value, S1.Pos, S2.L) as decimal(16,2)),
           stuff(S.Value, 1, S1.Pos + S2.L, '')
    from C as S
      cross apply (select patindex('%[0-9,.]%', S.Value)) as S1(Pos)
      cross apply (select patindex('%[^0-9,.]%', stuff(S.Value, 1, S1.Pos, ''))) as S2(L)
    where patindex('%[0-9,.]%', S.Value) > 0
  )
  select Number
  from C

Daniel Gimenez · Accepted Answer · 2013-06-24T20:39:45.630

1

Assuming your data is consistent, this should work fine, and has the added advantage of being easier on the eyes. Also consider decimal if you're going for precision.

select
  cast(left(r, charindex('%', r) - 1) AS float) as minVal,
  cast(replace(right(r, charindex('-', r) - 1), '%', '') as float) AS maxVal
from ( select '22.45% - 42.32%' as r ) as tableStub

edited Jun 24 '13 at 20:39

answered Jun 24 '13 at 20:23

Daniel Gimenez

18,530
3
50
70

score 0 · Answer 3 · answered Jun 24 '13 at 18:36

Here is a brute force approach using the string operations available in SQL Server:

with t as (
      select '12.45% - 42.32%'  as val
     )
select cast(SUBSTRING(val, 1, charindex('%', val) - 1) as float) as minval,
       cast(replace(substring(val, len(val) - charindex(' ', reverse(val))+2, 100), '%', '') as float) as maxval
from t

Extract multiple decimal numbers from string in T-SQL

3 Answers3