根据特定的子串短语从获取数值

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

Answer 2

从技术上讲，这将适用于您的上行问题，至少在您的示例数据范围内：

CASE WHEN RIGHT(TextField, 4) LIKE '%-[1234567890]' + '%' 
    THEN Left(Right(TextField,3),2) 
WHEN RIGHT(TextField, 6) LIKE '%to [1234567890]%'
    THEN Left(Right(TextField,3),2) 
WHEN RIGHT(TextField, 6) LIKE '%= [1234567890]%'
    THEN Left(Right(TextField,4),2) 
END AS UpperBound

但是，您的示例数据非常糟糕，m

Answer 3

请尝试此代码，并让我知道这是否适用于您的整个数据集。这里的假设是：

1)你的数字总是在“左心室射血分数”之后。

2)在“左心室射血分数”一词之后，除了定义下限和上界的数值外，没有其他数字。

3)下界和上界总是从左到右(从小到大)

4)起始表的名称是#temp

 declare @temp table (id int, field varchar(max))
 insert @temp

 select id, replace(right(Textfield, len(Textfield)-charindex('left ventricular ejection fraction', Textfield)+1),
'left ventricular ejection fraction','') field  
 from #temp 

 declare @holding table (id int, lowerbd varchar(max), upperbd varchar(max)) 
 declare @iterator int =1
 declare @prevfield varchar(max)
 declare @field varchar(max)
 declare @originalfield varchar(max)
 declare @number varchar(max)=''

 while @iterator<=(select max(id) from @temp)
 begin

 select @originalfield = field from @temp where id=@iterator

 while len(@originalfield) >0
 begin

 set @prevfield=@field

 select @field=left(@originalfield,1) 

 set @originalfield= 
 case 
 when len(@originalfield)>1 then substring(@originalfield, 2, len(@originalfield))
 else '' end 

 if  @field like '%[0-9]%'  
 begin
 set @number = @number+@field
 end
 if @field like '%[0-9]%' and @prevfield like '%[0-9]%'
 begin
     if not exists(select 1 from @holding where id= @iterator)
     begin
     insert @holding
     select @iterator, @number, null
     set @number=''
     end
     else   
     begin
     insert @holding
     select @iterator, null, @number
     set @number=''
     end

 end


end
set @iterator=@iterator+1
end

select id, max(lowerbd)lowerbd, max(upperbd)upperbd from @holding 
group by id