學海無邊，書囊無底

declare @numerics as table   
 
( row_id tinyint identity(1,1) primary key, numeric_val varchar(100))  
 
insert into @numerics(numeric_val)values  ('35.2'),(' 100.7(received tylenol per RN')
,('  120'),('100.6(@4pm)'),('92.4 orally'),('37.2(99.0)'),('35.3 C'),('-97.6')  

select n.numeric_val,  
try_convert(float, n.numeric_val) as nv_try_convert,  
isnumeric(n.numeric_val) as nv_isnumeric, case   
 when try_convert(float, n.numeric_val) is not null    
      then abs(try_convert(float, n.numeric_val))     
 when left(ltrim(n.numeric_val),4) like '[0-9][0-9].[0-9]'    
      then abs(try_convert(float, left(ltrim(n.numeric_val),4)))    
 when left(ltrim(n.numeric_val),5) like '[0-9][0-9][0-9].[0-9]'   
      then abs(try_convert(float, left(ltrim(n.numeric_val),5))) end as nv_try_convert_case   
 
from @numerics n

參考：红黑联盟