Let's take the excellent example from David and turn it into a iTVF instead of a scalar function.

create FUNCTION dbo.fnExtractDigits (@inString VARCHAR(8000))

RETURNS table

return

select SubString(

SubString(@inString, PATINDEX('%[0-9]%', @inString), Len(@inString)), 0,

PATINDEX('%[^.0-9]%', SubString(@inString,PATINDEX('%[0-9]%',@inString),Len(@inString)))) as MyValue

Now we can really leverage some power and performance.

--need to setup our test data

;with myCte(SomeKey, SomeValue) as

(

select 1, 'NYSTATIN SUSP 500000 UNIT = 5 ML (1 5 ML CUP)' union all

select 2, 'NYSTATIN SUSP 0.75 UNIT = 5 ML (1 5 ML CUP)'

)

--now we can retrieve it

select *

from myCte

cross apply dbo.fnExtractDigits(SomeValue) s