Get numeric values from based on specific substring phrase

Question

I have the alphanumeric text as below

 ID Textfield
 1  estimated left ventricular ejection fraction 60-65%
 2  estimated left ventricular ejection fraction is 55-60%
 3  Left ventricular ejection fraction is approximately 40 to 50%
 4  Fractional Short 50 %( 28-48) LV mass 83 gm (<220) systolic function  left ventricular ejection fraction = 52 % 

I need to extract numeric values for the left ventricular ejection fraction

Output should be

ID Lowerbound   Upperbound
1   60            65
2   55            60
3   40            50
4   52            NULL 

I have tried below sql syntax searching on character - its failing me for ID 3 and 4(For ID 4,its giving me as 50 but supposed to be 52)

SELECT SUBSTRING(textfield,CHARINDEX('-', 
textfield)-1,10),dbo.udf_GetNumeric(RIGHT(left(textfield,CHARINDEX('- 
',textfield)-1 ),10))AS Lower_bound,left(textfield, CHARINDEX('-', 
textfield) )


,dbo.udf_GetNumeric(SUBSTRING(textfield,CHARINDEX('-', textfield)+1,5)) 
    AS Upper_bound

Thank you in advance

Answer 1

This will use several CROSS APPLYs to get closer and closer. Some replacements make the string comparable, The first blank from the rear end is the border. The rest is rather easy.

DECLARE @tbl TABLE(ID INT, Textfield VARCHAR(500));
INSERT INTO @tbl VALUES
 (1,'estimated left ventricular ejection fraction 60-65%')
,(2,'estimated left ventricular ejection fraction is 55-60%')
,(3,'Left ventricular ejection fraction is approximately 40 to 50%')
,(4,'Fractional Short 50 %( 28-48) LV mass 83 gm (<220) systolic function  left ventricular ejection fraction = 52 % ');

SELECT ID
      ,Rev
      ,substr
      ,CASE WHEN hyph>0 THEN LEFT(substr,hyph-1) ELSE substr END AS LowerBound
      ,CASE WHEN hyph>0 THEN SUBSTRING(substr,hyph+1,10) ELSE NULL END AS UpperBound
FROM @tbl t
CROSS APPLY(SELECT REVERSE(RTRIM(REPLACE(REPLACE(t.Textfield,' to ','-'),' %','%')))) AS A(Rev)
CROSS APPLY(SELECT PATINDEX('% [^1-9]%',A.Rev)) AS B(pos)
CROSS APPLY(SELECT LTRIM(RTRIM(REPLACE(REVERSE(LEFT(A.Rev,B.pos)),'%','')))) AS C(substr)
CROSS APPLY(SELECT CHARINDEX('-',C.substr)) AS D(hyph);