How would one pull out the numeric text from within a varchar field? For example, I need to write a formula to determine overall dosage a patient could take in a given day. The instructions for the medication is stored as one long string like;
take 1 tablet by oral route every 4 hours as needed
I'd like to pull out the first numeric entry from the left and place it into a column named 'Quantity' and the first numeric entry from the right and put it in a column named 'Frequency'.
I'd then multiply this by the dose to determine the overall usage of a medication. Here is my current query;
select distinct p.person_id as [PID], p.last_name as [Last Name], p.first_name as [First Name], CAST(p.date_of_birth as DATE) as [Date of Birth],
pm.medication_name as [Medication],pm.sig_desc as [Sig], pm.start_date as [Start Date] from patient_medication pm
JOIN person p ON p.person_id = pm.person_id
JOIN fdb_medication f ON f.medid = pm.medid
where medication_name like '%hydromorphone%' and p.expired_ind <> 'Y' and (pm.date_stopped > GETDATE() or pm.date_stopped = '')
order by p.person_id
Thanks for any help!