January 4, 2017 at 9:00 am
The data represents the results of an echocardiogram. I need to get the numerical results out of this string. So, find the "%", then get the numbers in front. Problem is, there could be a value of <25%, or >65%. Any thoughts?
create table #T
(
TextID varchar(30),
TextLine varchar(300)
)
insert into #T(TextID, TextLine) values('14898341','The visually estimated ejection fraction is between 60-65%. There is')
insert into #T(TextID, TextLine) values('14898395',' The visually estimated ejection fraction is between 60-65%. ')
insert into #T(TextID, TextLine) values('14899043','The visually estimated ejection fraction is between 60-65%.')
insert into #T(TextID, TextLine) values('14899103',' The visually estimated ejection fraction is between 60-65%.')
insert into #T(TextID, TextLine) values('14899809','The visually estimated ejection fraction is between 60-65%.')
insert into #T(TextID, TextLine) values('14899867',' The visually estimated ejection fraction is between 60-65%.')
insert into #T(TextID, TextLine) values('14901939',' The visually estimated ejection fraction is between 60-65%. ')
insert into #T(TextID, TextLine) values('14902098','The visually estimated ejection fraction is between 60-65%.')
insert into #T(TextID, TextLine) values('14902160',' The visually estimated ejection fraction is between 60-65%.')
insert into #T(TextID, TextLine) values('15588094','nd wall motion. The visually estimated ejection fraction is between 60-65%. Right Ventricle Normal right ventricular cavity size, wall thickness, and systolic function. Atria Both atria are normal i')
insert into #T(TextID, TextLine) values('15475551','The left ventricular ejection fraction was calculated to be 71%. (Normal >50%).')
insert into #T(TextID, TextLine) values('15478850','t presents with acute on chronic diastolic congestive heart failure with ejection fraction around 60%. Seen by cardiologist and the current cardiothoracic team. Being contemplated for placement of an ')
insert into #T(TextID, TextLine) values('15486629','The left ventricular ejection fraction increases with stress. The ejection')
insert into #T(TextID, TextLine) values('15487054','Normal left ventricular systolic function with an ejection fraction of 55-60%')
January 4, 2017 at 10:03 am
This is what i slapped together to get started: i figured strip out anything Alpha might work, and preserve numbers, spaces and math symbols? but one of your examples had two values in it, which might not be what you were after.
SELECT *,LTRIM(RTRIM(fn.CleanedText)) FROM #T
CROSS APPLY master.dbo.StripAlphaCustom_ITVF(TextLine) fn
/*--Results
| (No column name)
| 60-65%
| 60-65%
| 60-65%
| 60-65%
| 60-65%
| 60-65%
| 60-65%
| 60-65%
| 60-65%
| 60-65%
| 71% >50%
| 60%
|
| 55-60%
*/
--EDIT whoops!
whoops forum converts html symbols, making an embedded version invalid: adding as attachment:
Lowell
January 4, 2017 at 10:04 am
Maybe something like this?:
select textid, textline,
SUBSTRING(textline, percent_byte - length_of_percent + 1, length_of_percent) as percent_amount
from #T
cross apply (
select CHARINDEX('%', textline) as percent_byte
) as assign_alias_names1
cross apply (
select case when percent_byte = 0 then 0
else CHARINDEX(' ', REVERSE(LEFT(textline, percent_byte - 1))) end as length_of_percent
) as assign_alias_names2
Edit: Added "+ 1" to the SUBSTRING starting byte calc.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 4, 2017 at 10:10 am
I'm not sure what your expected result is supposed to be, but I hope this helps you out.
WITH A AS (
SELECT TextID, TextLine
, PARSENAME(CASE CHARINDEX('%', TextLine)
WHEN 0 THEN ''
ELSE REPLACE(REVERSE(SUBSTRING(REVERSE(LEFT(TextLine, CHARINDEX('%', TextLine))), 2, CHARINDEX(' ', REVERSE(LEFT(TextLine, CHARINDEX('%', TextLine)))) - 2)), '-', '.')
END, 4) AS Part1
, PARSENAME(CASE CHARINDEX('%', TextLine)
WHEN 0 THEN ''
ELSE REPLACE(REVERSE(SUBSTRING(REVERSE(LEFT(TextLine, CHARINDEX('%', TextLine))), 2, CHARINDEX(' ', REVERSE(LEFT(TextLine, CHARINDEX('%', TextLine)))) - 2)), '-', '.')
END, 3) AS Part2
, PARSENAME(CASE CHARINDEX('%', TextLine)
WHEN 0 THEN ''
ELSE REPLACE(REVERSE(SUBSTRING(REVERSE(LEFT(TextLine, CHARINDEX('%', TextLine))), 2, CHARINDEX(' ', REVERSE(LEFT(TextLine, CHARINDEX('%', TextLine)))) - 2)), '-', '.')
END, 2) AS Part3
, PARSENAME(CASE CHARINDEX('%', TextLine)
WHEN 0 THEN ''
ELSE REPLACE(REVERSE(SUBSTRING(REVERSE(LEFT(TextLine, CHARINDEX('%', TextLine))), 2, CHARINDEX(' ', REVERSE(LEFT(TextLine, CHARINDEX('%', TextLine)))) - 2)), '-', '.')
END, 1) AS Part4
FROM #T
)
SELECT TextID, TextLine
, CASE
WHEN Part1 IS NULL AND Part2 IS NULL AND Part3 IS NULL THEN Part4
WHEN Part1 IS NULL AND Part2 IS NULL THEN Part3
WHEN Part1 IS NULL THEN Part2
ELSE Part1
END AS Num1
, CASE
WHEN Part1 IS NULL AND Part2 IS NULL AND Part3 IS NULL THEN NULL
WHEN Part1 IS NULL AND Part2 IS NULL THEN Part4
WHEN Part1 IS NULL THEN Part3
ELSE Part2
END AS Num2
, CASE
WHEN Part1 IS NULL AND Part2 IS NULL AND Part3 IS NULL THEN NULL
WHEN Part1 IS NULL AND Part2 IS NULL THEN NULL
WHEN Part1 IS NULL THEN Part4
ELSE Part3
END AS Num3
, CASE
WHEN Part1 IS NULL AND Part2 IS NULL AND Part3 IS NULL THEN NULL
WHEN Part1 IS NULL AND Part2 IS NULL THEN NULL
WHEN Part1 IS NULL THEN NULL
ELSE Part4
END AS Num4
FROM A
;
January 4, 2017 at 12:08 pm
Thanx everyone for your help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply