# 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)
``````

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.