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?