What is the best way to split a string into integers and units?

Question

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?

Answer 1

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)

Answer 2

I wrote two functions that do an OK job:

for the integer/decimal part:

    ALTER function [dbo].[get_dec](@string varchar(max))
    returns varchar(max)

    as
    begin

    declare
    @i int = 1,
    @end int = len(@string),
    @char varchar(max),
    @lastchar varchar(max)


    while @i <= @end 
        begin
        set @char = left(@string, @i)
        if isnumeric(@char) = 1 
            begin
            set @lastchar = @char
            end

        set @i = @i + 1
        end

    return @lastchar
    end

and for the units of measurement:

    ALTER function [dbo].[get_char](@string varchar(max))
    returns varchar(max)

    as
    begin

    declare
    @i int = 1,
    @end int = len(@string),
    @char varchar(max),
    @return varchar(max)


    while @i <= @end 
        begin
        if isnumeric(left(@string, 1)) = 1 or left(@string, 1) = '.'
            begin
                set @string = right(@string, len(@string) - 1)

            end
        set @i = @i + 1
        end

    return @string
    end

Your mileage may vary. These functions did ok on my server, for a few thousand rows.