What is the best way to split a string into integers and units? I've got a table of medication prices, each with their own units.
I am trying to split the strength into four columns: Numerator_integer, Numerator_uom, denominator_integer and denominator_uom.
I'm able to split the fraction into a numerator and denominator with a little game of 'find the slash' like so:
2GM/50ML becomes 2GM and 50ML
            if object_id('tempdb..#example') is not null begin drop table #example end
    
    create table #example (cost decimal(10,4), size decimal(10,4), strength varchar(255))

    insert into #example (cost, size, strength)
    values (918.060,    210.000,    '0.05MG/ML'),
            (808.480,       50.000, '1GM/50ML'),
            (1619.190,   50.000,    '2GM/50ML'),
            (1941.960,    1.000,    '300MG'),
            (30005.560,    40.000,  '5MG/ML')
    
    ;with costs2 as (select cost, size, strength, 
    case when strength like '%/%' then replace(left(strength, charindex('/', strength)),'/','')  else strength end as numerator, 
    case when strength like '%/%' then right(strength, len(strength) - charindex('/', strength)) else '1' end as denominator 
    from #example),

    costs as (select *, null as numerator_uom, null as denominator_uom from costs2)

    select * from costs

However I can't think of an elegant way to loop through the numerator and split out the numeric half from the varchar half.
Question: how would you split out the numeric and varchar halves of a string?
We can use APPLY to calculate the position of the various things we need, we use each APPLY in series like variables in procedural code.
We assume that the number is the start of the string, followed by arbitrary letters, a slash, another number, then more letters.
Note: no tally function needed here:
            
select
    cost,
    size,
    strength,
    v4.*

from example e

cross apply ( values (
    charindex('/', e.strength)
) ) as v1 (slash)

cross apply ( values (
    iif(slash = 0, e.strength, left(e.strength, slash - 1)),
    substring(e.strength, nullif(slash, 0) + 1, len(e.strength)) -- len means the rest of string
) ) as v2 (numer, denumer)

cross apply ( values (
    patindex('%[^0-9.]%', numer),  -- first non-number
    patindex('%[^0-9.]%', denumer)
) ) as v3 (numerUnit, denumerUnit)

cross apply ( values (
    try_cast(iif(numerUnit = 0, numer, left(numer, numerUnit - 1)) as real),
    iif(numerUnit = 0, null, substring(numer, numerUnit, len(numer))),

    try_cast(iif(denumerUnit = 0, denumer, left(denumer, denumerUnit - 1)) as real),
    iif(denumerUnit = 0, null, substring(denumer, denumerUnit, len(denumer)))
) ) as v4 (numerator, numeratorUnit, denumerator, denumeratorUnit)