T-SQL SUBSTRING with REPLACE and CASE

Hi,

I have been tasked with trying to abstract some data from our dental database with the following criteria

Anesthetic Used
# of Carpules used
Patient Name and Chart number
Dr. Names.

I have ran into an issue where the anesthetic and carpule information is located in a single column called notes.  The notes for the most part follow a standard method of entering them but in some cases the Dr's make adjustments to them making it hard to extract the proper information

Is there a better way to extract the data than the way I have been doing it

below is my code

use Dental

select p.last_name, p.school as [chart number], 'Dr. ' + pr.last_name, b.date_entered, b.description, note as [actual notes],
REPLACE(SUBSTRING(b.note, CHARINDEX('Anesthetic', b.note) + -1, 150), '.','')as [Anesthetic Notes SubString],

case when
REPLACE(REPLACE(REPLACE(SUBSTRING(b.note, CHARINDEX('Anesthetic', b.note) + -1, 72), '.',''), 'anesthetic used -', ''),' anesthetic used-', '') like 
'%2% lidocaine with 1:100k epinephrine,%' then '2% lidocaine with 1:100k epinephrine'
when
REPLACE(REPLACE(REPLACE(SUBSTRING(b.note, CHARINDEX('Anesthetic', b.note) + -1, 72), '.',''), 'anesthetic used -', ''),' anesthetic used-', '') Like
'%2% xylocaine without epi%' then '2% xylocaine without epi'
when
REPLACE(REPLACE(REPLACE(SUBSTRING(b.note, CHARINDEX('Anesthetic', b.note) + -1, 72), '.',''), 'anesthetic used -', ''),' anesthetic used-', '') like 
'%3% carbocaine without epinephrine,%' then '3% carbocaine without epinephrine'
when
REPLACE(REPLACE(REPLACE(SUBSTRING(b.note, CHARINDEX('Anesthetic', b.note) + -1, 72), '.',''), 'anesthetic used -', ''),' anesthetic used-', '') Like
'%2% lidocaine with 1:100k epinephrinetopical anesth%' then '2% lidocaine with 1:100k epinephrinetopical anesthetic'
when
REPLACE(REPLACE(REPLACE(SUBSTRING(b.note, CHARINDEX('Anesthetic', b.note) + -1, 72), '.',''), 'anesthetic used -', ''),' anesthetic used-', '') Like
'%topical anesthetic,%' then 'Topical Anesthetic'
when
REPLACE(REPLACE(REPLACE(SUBSTRING(b.note, CHARINDEX('Anesthetic', b.note) + -1, 72), '.',''), 'anesthetic used -', ''),' anesthetic used-', '') Like
'%none,%' or REPLACE(REPLACE(REPLACE(SUBSTRING(b.note, CHARINDEX('Anesthetic', b.note) + -1, 72), '.',''), 'anesthetic used -', ''),' anesthetic used-', '') Like 
'%, carpules used,%' or REPLACE(REPLACE(REPLACE(SUBSTRING(b.note, CHARINDEX('Anesthetic', b.note) + -1, 72), '.',''), 'anesthetic used -', ''),' anesthetic used-', '') Like 
',0 carpules used,%'
then 'None' 
when
REPLACE(REPLACE(REPLACE(SUBSTRING(b.note, CHARINDEX('Anesthetic', b.note) + -1, 72), '.',''), 'anesthetic used -', ''),' anesthetic used-', '') Like
'%4% Prilocaine without epinephrine,%' then '4% Prilocaine without epinephrine'
when
REPLACE(REPLACE(REPLACE(SUBSTRING(b.note, CHARINDEX('Anesthetic', b.note) + -1, 72), '.',''), 'anesthetic used -', ''),' anesthetic used-', '') Like
'%3% carbocaine without epinephrinetopical anesthetic,%' then  '3% carbocaine without epinephrinetopical anesthetic'
when
REPLACE(REPLACE(REPLACE(SUBSTRING(b.note, CHARINDEX('Anesthetic', b.note) + -1, 72), '.',''), 'anesthetic used -', ''),' anesthetic used-', '') Like
' 3% carbocaine without epinephrine 2% lidocaine with%' then '3% carbocaine without epinephrine 2% lidocaine with 1:100k epinephrine topical anesthetic'
when
REPLACE(REPLACE(REPLACE(SUBSTRING(b.note, CHARINDEX('Anesthetic', b.note) + -1, 72), '.',''), 'anesthetic used -', ''),' anesthetic used-', '') Like
'%2% lidocaine with 1:100k epinephrine05% marcaine with%' then '2% lidocaine with 1:100k epinephrine05% marcaine with 1:200K epinephrine topical anesthetic'
when
REPLACE(REPLACE(REPLACE(SUBSTRING(b.note, CHARINDEX('Anesthetic', b.note) + -1, 72), '.',''), 'anesthetic used -', ''),' anesthetic used-', '') Like
'%3% carbocaine without epinephrine2% lidocaine with 1:1' then '3% carbocaine without epinephrine2% lidocaine with 1:100k epinephrine topical anesthetic'

else
REPLACE(REPLACE(REPLACE(SUBSTRING(b.note, CHARINDEX('Anesthetic', b.note) + -1, 72), '.',''), 'anesthetic used -', ''),' anesthetic used-', '')
end Anesthetic,
REPLACE(SUBSTRING(b.note, CHARINDEX('carpule', b.note) + -2, 1), ',','0') as Carpules
from operatory_notes b
inner join patient p on b.patient_id = p.patient_id
inner join provider pr on b.provider_id = pr.provider_id
where b.note like '%Anesthetic%'

Select allOpen in new window