July 20, 2011 at 11:51 am
Here is an example of the data that I am working with
<OBX.5><OBX.5.1>HospitalName CityName</OBX.5.1><OBX.5.2/><OBX.5.3> EMERGENCY RECORD</OBX.5.3><OBX.5.4/><OBX.5.5/><OBX.5.6>PATIENT: LastName , FirstName</OBX.5.6><OBX.5.7>MR#: 000701057460 ACCT#: 000101010101</OBX.5.7><OBX.5.8>DATE OF SERVICE: 05/31/1909</OBX.5.8><OBX.5.9/><OBX.5.10>DATE AND TIME SEEN:</OBX.5.10><OBX.5.11>In the emergency department on 5/31/09 at 1513.</OBX.5.11><OBX.5.12/><OBX.5.13>CHIEF COMPLAINT:</OBX.5.13><OBX.5.14>Left wrist and elbow injury.</OBX.5.14><OBX.5.15/><OBX.5.16>MODE OF ARRIVAL:</OBX.5.16><OBX.5.17>Private vehicle. The patient is a reliable historian.</OBX.5.17><OBX.5.18/><OBX.5.19>HISTORY OF PRESENT ILLNESS:</OBX.5.19><OBX.5.20>A pleasant 63-year-old right-hand dominant male who was riding a</OBX.5.20><OBX.5.21>motorcycle on Sunday, approximately 30 miles an hour when he</OBX.5.21><OBX.5.22>accidentally fell and injured his left wrist and elbow. He was</OBX.5.22><OBX.5.23>wearing a helmet as well as full gear. He denies any head</OBX.5.23><OBX.5.24>injury, loss of consciousness. He denies any chest pain,</OBX.5.24><OBX.5.25>abdominal pain, low back pain. He did sustain a small puncture</OBX.5.25><OBX.5.26>wound and abrasion to his elbow and arm. He believes his tetanus</OBX.5.26><OBX.5.27>immunization is current. He denies any aspirin or blood thinning</OBX.5.27><OBX.5.28>products. No other complaints at this time.</OBX.5.28><OBX.5.29/><OBX.5.30>MEDICATIONS:</OBX.5.30><OBX.5.31>None.</OBX.5.31><OBX.5.32/><OBX.5.33>ALLERGIES:</OBX.5.33><OBX.5.34>NO FOOD OR DRUG ALLERGIES.</OBX.5.34><OBX.5.35/><OBX.5.36>PAST MEDICAL HISTORY:</OBX.5.36><OBX.5.37>He is generally healthy and denies any heart disease, lung</OBX.5.37><OBX.5.38>disease, GI ulcer, bleeds, kidney or liver dysfunction, or</OBX.5.38><OBX.5.39>diabetes. He has had several fractures in the past.</OBX.5.39><OBX.5.40/><OBX.5.41>PAST SURGICAL HISTORY:</OBX.5.41><OBX.5.42>Femur, left hip, knee surgery.</OBX.5.42><OBX.5.43/><OBX.5.44>SOCIAL HISTORY:</OBX.5.44><OBX.5.45>Denies any tobacco use. Occasional alcohol use. He is a local</OBX.5.45><OBX.5.46>resident. Works for StarDucks Manufacturing.</OBX.5.46><OBX.5.47/><OBX.5.48>REVIEW OF SYSTEMS:</OBX.5.48><OBX.5.49>CONSTITUTIONAL: No significant weight change.</OBX.5.49><OBX.5.50>EYES: No recent change in vision.</OBX.5.50><OBX.5.51>ENT/MOUTH: No recent change in hearing.</OBX.5.51><OBX.5.52>RESPIRATORY: No cough.</OBX.5.52><OBX.5.53>CARDIOVASCULAR: No palpitations.</OBX.5.53><OBX.5.54>GASTROINTESTINAL: No change in bowel habit.</OBX.5.54><OBX.5.55>GENITOURINARY: No dysuria.</OBX.5.55><OBX.5.56>MUSCULOSKELETAL/EXTREMITIES: See history of present illness.</OBX.5.56><OBX.5.57>SKIN: See history of present illness.</OBX.5.57><OBX.5.58>NEUROLOGIC: See history of present illness.</OBX.5.58><OBX.5.59/><OBX.5.60>PHYSICAL EXAMINATION:</OBX.5.60><OBX.5.61>VITAL SIGNS: Temperature is 36.6, pulse 68, respirations 18,</OBX.5.61><OBX.5.62>blood pressure 131/75, O2 saturation is 96%.</OBX.5.62><OBX.5.63>GENERAL: This is a well-developed, well-nourished male, in no</OBX.5.63><OBX.5.64>acute distress. He is alert and oriented x4, nontoxic appearing.</OBX.5.64><OBX.5.65>HEENT: Scalp is atraumatic, normocephalic. Cervical spine is</OBX.5.65><OBX.5.66>without tenderness of the midline.</OBX.5.66><OBX.5.67>CHEST: Clear to auscultation bilaterally. No wheeze, rhonchi,</OBX.5.67><OBX.5.68>or rales.</OBX.5.68><OBX.5.69>CARDIOVASCULAR: Regular rate, rhythm. There is evidence of a</OBX.5.69><OBX.5.70>grade 2/6 systolic murmur. No gallops present.</OBX.5.70><OBX.5.71>ABDOMEN: Soft, nontender.</OBX.5.71><OBX.5.72>EXTREMITIES: The patient moves right upper extremity without</OBX.5.72><OBX.5.73>difficulty. On the left, there is a moderate amount of soft</OBX.5.73><OBX.5.74>tissue swelling noted over the dorsum of the hand. Tenderness</OBX.5.74><OBX.5.75>noted over the wrist.</OBX.5.75><OBX.5.76>SKIN: Intact. The patient has abrasion on the arm of</OBX.5.76><OBX.5.77>approximately 2 cm. There is also an abrasion on the elbow</OBX.5.77><OBX.5.78>approximately 1.5 cm. No tenderness is noted over the clavicle</OBX.5.78><OBX.5.79>or the shoulder. The patient is able to fully flex and extend</OBX.5.79><OBX.5.80>the elbow. Pulses are +2. Capillary refill is brisk and less 2</OBX.5.80><OBX.5.81>to 3 seconds.</OBX.5.81><OBX.5.82/><OBX.5.83>CLINICAL DECISION MAKING:</OBX.5.83><OBX.5.84>During his stay here in the emergency department, I reviewed his</OBX.5.84><OBX.5.85>previous records.</OBX.5.85><OBX.5.86/><OBX.5.87>A three-view x-ray of his left elbow was obtained secondary to</OBX.5.87><OBX.5.88>trauma. There is evidence of what appears to be a possible small</OBX.5.88><OBX.5.89>avulsion fracture noted. No step-off noted. No anterior or</OBX.5.89><OBX.5.90>posterior hemarthrosis present. This is per my reading, per my</OBX.5.90><OBX.5.91>interpretation. X-ray findings discussed with the patient in</OBX.5.91><OBX.5.92>detail.</OBX.5.92><OBX.5.93/><OBX.5.94>A three-view x-ray of his left wrist was obtained secondary to</OBX.5.94><OBX.5.95>trauma. There is evidence of an ulnar styloid fracture present.</OBX.5.95><OBX.5.96> There is also evidence of an intra-articular comminuted,</OBX.5.96><OBX.5.97>minimally displaced distal radius fracture. Soft tissue swelling</OBX.5.97><OBX.5.98>is present. This is per my reading, per my interpretation.</OBX.5.98><OBX.5.99> X-ray findings discussed with the patient in detail.</OBX.5.99><OBX.5.100/><OBX.5.101>Volar splint applied. He is neurovascularly distally intact post splint </OBX.5.101><OBX.5.102>application, which was done under my direct supervision. A sling was also </OBX.5.102><OBX.5.103>provided for comfort and support as well.</OBX.5.103><OBX.5.104/><OBX.5.105>Nursing personnel cleansed the wound, irrigated and dressed them</OBX.5.105><OBX.5.106>appropriately.</OBX.5.106><OBX.5.107/><OBX.5.108>At this point, I do feel the patient can be monitored on an</OBX.5.108><OBX.5.109>outpatient basis. I do not find there is any evidence of</OBX.5.109><OBX.5.110>neurovascular compromise. I do feel the patient would do well in</OBX.5.110><OBX.5.111>an outpatient setting.</OBX.5.111><OBX.5.112/><OBX.5.113>CLINICAL DIAGNOSES:</OBX.5.113><OBX.5.114>1. Acute left comminuted intraarticular distal radius fracture.</OBX.5.114><OBX.5.115>2. Acute left ulnar styloid fracture.</OBX.5.115><OBX.5.116>3. Laceration, abrasions left elbow, left arm.</OBX.5.116><OBX.5.117>4. Motorcycle accident.</OBX.5.117><OBX.5.118/><OBX.5.119>PLAN:</OBX.5.119><OBX.5.120>The patient was given a prescription for Tylonel 3, quantity 25</OBX.5.120><OBX.5.121>with instruction, 1 p.o. q.4 to q.6 hours p.r.n. pain. He is not</OBX.5.121><OBX.5.122>to drink, drive, nor operate a machine while taking the above</OBX.5.122><OBX.5.123>medication. He is to keep the arm elevated to help reduce the</OBX.5.123><OBX.5.124>swelling. Keep the wound clean and dry. Wound check in two to</OBX.5.124><OBX.5.125>three days. Follow up at the Core Orthopedic Group. Films</OBX.5.125><OBX.5.126>provided.</OBX.5.126><OBX.5.127/><OBX.5.128>DISPOSITION:</OBX.5.128><OBX.5.129>The patient was discharged home ambulatory in stable condition.</OBX.5.129><OBX.5.130> He voices understanding all discharge instructions, has no</OBX.5.130><OBX.5.131>further questions, and will return to the emergency department if</OBX.5.131><OBX.5.132>symptoms worsen, do not improve. Otherwise, to follow up with</OBX.5.132><OBX.5.133>the above recommendations.</OBX.5.133><OBX.5.134/><OBX.5.135/><OBX.5.136/><OBX.5.137/><OBX.5.138/><OBX.5.139>DICTATED BY: Snuffy Smith, PAC Elmer Fudd, MD</OBX.5.139><OBX.5.140/><OBX.5.141/><OBX.5.142/><OBX.5.143>NKC:Almond12345 C: 06/01/09 04:17 CONFIRMATION #: 225713</OBX.5.143><OBX.5.144>D: 05/31/11 23:30 T: 06/01/09 04:17 DOCUMENT: 190906010000101010101</OBX.5.144><OBX.5.145>Authenticated and Edited by Bugs Bunny, P.A. On 6/07/09 2:15:07 PM </OBX.5.145><OBX.5.146>Authenticated by Elmer Fudd, M.D. On 06/10/1909 12:44:00 PM </OBX.5.146><OBX.5.147/></OBX.5>
I need all the tags removed.
Andrew SQLDBA
July 20, 2011 at 11:55 am
Ninja's_RGR'us (7/20/2011)
What happens if the text outside the tags containt < or > ?I never fully tackled this problem, but I know it's far from simple.
Moreover I think that RBARing this might be a good option to try. Right now you're hitting and updating the same row a ton of times. I'd preffer using a itvf or even a simple function, loop in it and then update the base row only once (read / write once max).
Ninja is right; if the rest of the text is malformed html, my example would throw up.
another option would be leaping ot CLR and regular expressions, but i was trying to stay in my "homezone" of tsql examples.
Lowell
July 20, 2011 at 12:01 pm
That's why I don't have an alternative to offer. I don't know regex and I didn't want to learn sqlclr for just that one project.
This is very far from a simple project (I'm sure Pault White would disagree with me so I'll ping him in!).
July 20, 2011 at 1:50 pm
Lowell
That did the trick, the code that you gave worked perfectly.
Thank you very much to everyone. I greatly appreciate all the suggestions and help from each of you
Andrew SQLDBA
July 20, 2011 at 2:12 pm
I developed comments for HL7 to Admit Transfer and Discharge Patients as well as billing.
There are a number of different protocols ( I remember three primary).
You have to have a solid grasp of the architecture.
It is not easy. I was handed a project and I was on my own.
It is not something that you perform in SQL, it is handled in C# or VB.NET.
I started out by goggling and I figured out how to interact with a system that activated TV's and services, deactivated, feed information to the billing ancillary system.
I'm sorry but it has been so long since I did this (10+ years).
I would have to do a lot of goggling and research to help you.
Regards,
WC:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 20, 2011 at 3:56 pm
If the tags are well-formed, this will work:
CREATE TABLE MySampleData (ID INT,HTMLText TEXT)
INSERT INTO MySampleData(ID,HTMLText)
SELECT 1,'NoHTML Inside' UNION ALL
SELECT 2, '<Tag1><tag1.1>yada</tag1.1></Tag1>' UNION ALL
SELECT 3 ,'<OBX.5><OBX.5.1>Blah Blah Data</OBX.5.1></OBX.5>Other Stuff outside of the tags'
SELECT
msd.ID,
CAST(msd.HTMLText AS XML)
.query('//./text()')
.value('.', 'VARCHAR(MAX)')
FROM dbo.MySampleData AS msd;
DROP TABLE dbo.MySampleData;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 20, 2011 at 6:43 pm
Thanks for the assist Paul.
Does that handle single < or > in the text part of the tags? (out of office so I can't run a test).
Is there a way to do this in clr with regex that is stunningly fast vs using sql server's xml?
July 20, 2011 at 9:33 pm
Ninja's_RGR'us (7/20/2011)
Does that handle single < or > in the text part of the tags? (out of office so I can't run a test).
Nope, cos that wouldn't be well-formed XML (those characters would be written as & lt; or & gt;)
Is there a way to do this in clr with regex that is stunningly fast vs using sql server's xml?
Yes.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 22, 2011 at 8:19 am
I don't understand why you would even be trying to adjust HL7 messages within SQL. Most Health facilities use an interface engine such as Biztalk or Rhapsody to name a few, to make adjustments to the messages before passing them on to another application.
Your example of the ADT message. What type of ADT message is it? Admission, Discharge, Transfer, Patient Information Update, Merge, PreAdmit, Inpatient, Outpatient, ect? Each type will have it's own required and non-required fields.
If you start looking at orders then you get into repeating segments. My suggestion is to look at the ASCII version of the message. Know the segments, fields, if they are repeatable, and remember each vendor that has a HL7 message does not always include anything extra in the message than what is required. Which means when sending the message on to another application you may have to add, remove, or alter the data.
For those that haven't worked with HL7 here is a link to info regarding the ADT which was mentioned in the original question
July 22, 2011 at 8:23 am
Kath,
Thank you for adding your insight. I think only one of the previous respondents works with HL7, so your perspective is very welcome indeed.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply