sql - 根据特定的子字符串短语获取数值

Question

我有如下字母数字文本

 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 % 

我需要提取左心室射血分数的数值

输出应该是

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

我尝试了以下 sql 语法搜索字符 - 它的 ID 3 和 4 失败(对于 ID 4，它给我 50 但应该是 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

先感谢您

Answer 1

这将使用几个 CROSS APPLYs越来越近。一些替换使字符串具有可比性，从后端开始的第一个空格是边框。剩下的就比较简单了。

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);