troubleshooting Question

Pull numeric text out of a varchar string

Avatar of CMCITD
CMCITDFlag for United States of America asked on
Microsoft SQL Server 2008SQL
13 Comments1 Solution157 ViewsLast Modified:
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!
Join our community to see this answer!
Unlock 1 Answer and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros