?
Solved

T-SQL SUBSTRING with REPLACE and CASE

Posted on 2011-10-20
4
Medium Priority
?
235 Views
Last Modified: 2012-05-12
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%'

Open in new window

0
Comment
Question by:thomasm1948
  • 2
  • 2
4 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 37003748
Given what you have to work with I'd say it's possibly your only option.
But (as always with free format text) don't expect the results to be bulletproof.
Might be time for a design rethink if able/practical
0
 

Author Comment

by:thomasm1948
ID: 37003767
Attached is sample data of what i have am querying
SQL-Data-Extract.xlsx
0
 
LVL 29

Accepted Solution

by:
QPR earned 2000 total points
ID: 37003781
That doesn't look too bad (could be much worse). I'd say this reinforces what you have coded given the uniform nature of the data in the field
0
 

Author Closing Comment

by:thomasm1948
ID: 37007837
Thank you for your help
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question