I have a table stat that is VARCHAR type and would like to grab all the numbers within it individually. For example, if a specific record in the column has the value 12 to 24 MONTHS Fl then I would like to grab 12 and 24 separately. I've seen other posts where the numbers end up grouped together and would be 1224 in this case, but how could I do the aforementioned separating of the numbers? Given that I do not know the number of digits in each of the numbers, I was wondering how best to do this. Thanks

For an example like 12 to 24 months APY1.8semi the output would need to be 12,24 and 1.8, but to be clear there are only whole numbers and there aren't any . characters in the column.

CodePudding user response：

First we create this function.

   create function [dbo].[GetNumbersFromText](@String varchar(2000))
    returns table as return
    (
      with C as
      (
        select cast(substring(S.Value, S1.Pos, S2.L) as decimal(10,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(10,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
    )

Then we use it in our query.

select string_agg(number, ', ') as result from GetNumbersFromText('12 to 24 months APY1.8semi')

Fiddle

CodePudding user response：

I shamelessly copied this answer from another post but made a small modification to preserve your spaces. This one is essentially replacing letters with the @ symbol, then replacing the @ symbol.

select id, REPLACE(TRANSLATE([comments], 'abcdefghijklmnopqrstuvwxyz ()- ,# ', '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'), '@', ' ')
from my_data

fiddle

Or if you would prefer results as a tall table, then you could apply the string_split function.

select id, value from (
  select id, ca.value
  from my_data
  cross apply string_split (REPLACE(TRANSLATE([comments], 'abcdefghijklmnopqrstuvwxyz ()- ,# ', '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'), '@', ','), ',')ca
  )z
where value <> ''

fiddle