Pull numeric text out of a varchar string

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

Open in new window


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 

Open in new window


Thanks for any help!
CMCITDAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
If you want to do it just in SQL then you could build off something like this.  It's a bit wordy, but should get the job done.  You didn't mention what table / column the text is in, so for this example I assumed x.usage is the field to be processed.  This also assumes there are two numbers in the data, if there might not be then you would have to filter around those also.  Once you extract the values you can use as needed...

SELECT SUBSTRING(x.usage, PATINDEX('%[0-9]%', x.usage), CHARINDEX(' ', x.usage, PATINDEX('%[0-9]%', x.usage))-PATINDEX('%[0-9]%', x.usage)) AS [Quantity],
REVERSE(SUBSTRING(REVERSE(x.usage), PATINDEX('%[0-9]%', REVERSE(x.usage)), CHARINDEX(' ', REVERSE(x.usage), PATINDEX('%[0-9]%', REVERSE(x.usage)))-PATINDEX('%[0-9]%', REVERSE(x.usage)))) AS [Frequency]
FROM table1 x;

Open in new window


»bp
0
CMCITDAuthor Commented:
Thanks for the help Bill.  It threw an error;  Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

Also--how would I incorporate that into the existing query? Thanks again!
0
Bill PrewCommented:
What was the text value of the column you were referencing, for the row in question?

To incorporate it into the query I would need to know the table and column name where the usage information exists.


»bp
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

CMCITDAuthor Commented:
Sorry didn't state before;  The table = patient_medication and the column = sig_description

Not sure what row is causing the error but here is some example results before it errors out;

1	12
1	5
1	12
1	4
3	3
apply	area(s)
3.5G)	(3.5
5	8
apply	area(s)
3	3
1	4
apply	area(s)
4	4
6	4
1	1
1	2
1	1
1	2
1	2
1	1
1	(40
1	(40
1	1
1	(40
1	1
1	(40
1	2
1	2
1	(40
1	1
1	1
1	1
1	1
1	(40
1	1
1	1
1	1
1	1
1	(40
1	1
1	2
1	2
1	2
1	2
1	2
1	2
1	2
1	2
1	2
1	2
1	2
1	2
1	(50
1	1
1	(40
1	(0.5
1	2
1	1
1	6
1	(0.5
1	2
1	1
2	2
1	6
1	1
1	1
1	12
1	(20
1	2
1	24
1	(37.5
15	30
1/2	24
2MG)	7
1	1
1	(37.5
1	(20
1	(3
1	12
1	2
1/2	8
2	6
1	(3
1	2
1-2	6
1	(10
1	12
1	1
1/2	2
1	1
1	12
2G)	2
1	1
1	3
1	2
1	12
1	2
2MG)	7
1	6
1	12
1	1
1	12
2	4
1	(250
1/2	6
1	(20
1	1
1	12
1	12
1	4
1	(20
1	12
1/2	6
1	12
2-3	2-3
1	2
1	1
1	3
1	3
1	4
1	1
1	3
1	(10
1	4
1/2	1/2
2-3	2-3
1	1
1	2
TAKE	DAY
USE	DIRECTED
1	2
15	6
1	1
1	(25
2	(200
take	day
TAKE	DAY
TAKE	DAY
1	1
1	3
1	2
1	2
1	1
1	6
1	1
2-3	2-3
1	1
1	2
1	3
1	2
1	2
USE	DIRECTED
1	1
2-3	2-3
1/2	1/2
1	2
1	4
1	4
1	(320
1	1
1	(60
TAKE	DAY
1	(10
1	1
1	1
1	6
1	72
2	2
1	1
1	6
1	1
1	8
1	1
1	1
1	1
1	1
2	6
1	1
1	2
1	1
1	1
3	4
1	1
50	50
inject	individualization.
1	2
1	8
2	6
1	2
2	2
1	1
1	1
1	1
1	1
2	6
2	2
1	4
take	needed
take	needed
1	12
2	100
2	100
1	4
TAKE	INSOMNIA
1	4
1	1
22	(3300
1	4
1	2
1	6
1	1
1	4
take	needed
1	(0.625
2	2
1	4
1	7
1	4
1	4
1	12
1	3
1	4
1	2
1	12
6	(1200
1	12
1	1
1	4
1G)	(1
2	2
1	12
3	(600
2	2
1	(0.625
1	1
17G)	8
4	(800
2	100
place	food
1	4
2	2
1	4
1	1
1	2
3	(60
1	12
1	4
1	4
TAKE	INSOMNIA
take	needed
1	1
1	2
2	2
2	7
take	needed
1	(125
2	4
1	4
2	4
take	needed
1	3
AS	DIRECTED
1	2
2	2
1	1
2	10
5	5
1	4
1	(20
1	4
2	100
1	1
1	1
2	2
take	needed
TAKE	INSOMNIA
1	4
1	4
1	1
1	4
1	4
1	4
1	2
1	2
2	2
take	needed
1	4
1	4
1	1
TAKE	INSOMNIA
2	2
1	4
1	2
4	4
1	4
1/2	3
2	2
1	4
1	4
2	100
1	1
1	1
2	(600
1	4
1	1
1	2
2	100
1	4
1	4
take	needed
1	1/2
1	2
6	(1200
1	(20
1	3
1	4
1	4
1	1
take	needed
1	(10
5	5
3	(60
1	(50
1	4
1	4
1	1
1	2
2	2
1	24
1	1
1	4
1	(125
1	2
1	1
1	1
1	4
1	4
1	4
1	4
1/2	3
1	3
2	4
TAKE	INSOMNIA
1	2
6	(1200
2	2
1	(125
1	1
take	needed
1	4
1	12
1	10
1	4
1	4
1	2
drink	liquids
1	1
1	(125
2	3
2	4
1	4
1	(125
1	12
1	1
2	2
1CM)	3
1	4
1	4
take	needed
1	4
5	5
1	4
1	1
1	2
1	1
1	4
1	4
1	(10
3	3
TAKE	DAY
TAKE	INSOMNIA
1	4
1	(40
1	1
TAKE	INSOMNIA
TAKE	INSOMNIA
2	2
1	1
1	4
TAKE	DAILY
1	4
2	3
1	4
1	12
2	100
1	1
TAKE	INSOMNIA
take	needed
2	(600
1	1
1	1
1	4
1	4
TAKE	INSOMNIA
1	2
1	4
1	2
1	1
2	4
1	2
1	1
1	4
1	1
1	3
1	8
place	food
1	8
1	3
1	1
1	3
1	6
1-2	4
1-2	4
1	12
1	3
15	4
1-2	4
15	4
1	3
1	3
1/2	1/2
	
1	1
1	2
1	6
2	4
1	3
1	1
1	1
2	4
1	1/2
1	12
1	4
2	2
4	10
2	4
1	10
4	4
5	8
4	4
1	4
1.5	1
1	4
1	1
1	2
1	3
1	2
1	1
1	3
1	1
2	2
1	1
1	3
1	6
1	6
1	2
1-2	3
1	6
1	2
1	2
1	2
1	1
2	2
1	1
1	6
2	2
2	2
1	3
1	3
1	2
1-2	3
1	1
1	3
2	4
1	2
1	6
1	6
1	2
TAKE	DAY
2	2
1	1
2	5
1	1
4	7
1-2	1-2
1	6
1	1
1-2	3
1	1
1	6
1	1
2	2
1	1
1	7
1	1
1	1
1	1
1	3
1	1
1	3
1	1
1	1
1	1
4	7
4	7
17G)	8
1	1
3	3
1	2
1	2
2	2
2	2
1	1
1	6
1	1
1	2
17	8
1	1
1	1
1	1
1	2
1	4
1	6
1	4
1	3-4
1	3
1	6
1	1
1	6
1	2
1	1
1	1
1	1
1	2
1	2
1	1
1	2
4	7
4	7
1	6
1	3
1	6
1	2
3	3
1	1
4	7
2	2
1	1
1	6
1	1
3	3
1	2
1	1
1	1
1	1
1	1
1	1
1	12
1	1
1	1
1	12
1	4
1	2
1	2
1	3
1	2
1	1
1	1
1	1
1	1
TAKE	DAY
1	1
1	1
1	1
1	1
1	3
1	1
1	10
1	12
1	1
1	1
2	4
1	2
1	1
1	10
1	1
1	1
1	1
1	2
take	day
1	1
1	2
1	4
1	12
1,2,	2-4
72	72
1	6
1	(25
1	2
1	1
TAKE	NEEDED
1-2	1-2
3	(25
1	6
1	(75
1	4
1	6
1-2	1-2
1	1
1	72
1-2	1-2
1-2	1-2
1	12
1	6
1	4
1-2	1-2
1	1
1	2
1-2	1-2
1	6
1	4
1-2	1-2
0.25	2
1	4
30mL	2
1	10
1	12
1	2
1	12
1	1
1	1
0.25	4
TAKE	NEEDED
1	4
1	6
TAKE	NEEDED
72	72

Open in new window

0
Bill PrewCommented:
It would be helpful if you could also display the sig_description column in that output.

I also assumed that the numbers would be followed by a space, doesn't look like that is always the case...

I would expect that the error is coming from a row where there is no numbers in that column, but you would have to see if that exists.


»bp
0
Scott PletcherSenior DBACommented:
SELECT
    string,
    CASE WHEN first_number_start = 0 THEN ''
         ELSE SUBSTRING(string, first_number_start, first_number_length)
         END AS number1,
    CASE WHEN first_number_start = 0
                    OR first_number_start =(last_number_end - last_number_length + 1)
              THEN ''
              ELSE SUBSTRING(string, last_number_end - last_number_length + 1, last_number_length)
              END AS number2
FROM ( VALUES
    ('take 1 tablet by oral route every 4 hours as needed'),
    ('take 1 tablet by oral route every 40 hours as needed'),
    ('take 10 tablet by oral route every 4 hours as needed'),
    ('take 10 tablet by oral route every 40 hours as needed'),
    ('take 1 tablet'),
    ('take nothing, you''re fine, go home')
) AS data(string)
CROSS APPLY (
    SELECT PATINDEX('%[0-9]%', string) AS first_number_start,
        LEN(string) - PATINDEX('%[0-9]%', REVERSE(string)) + 1 AS last_number_end
) AS ca1
CROSS APPLY (
    SELECT PATINDEX('%[^0-9]%', SUBSTRING(string + '.', first_number_start, 100)) - 1
        AS first_number_length,
        PATINDEX('%[^0-9]%', REVERSE(LEFT(string, last_number_end))) - 1 AS last_number_length
) AS ca2
0
CMCITDAuthor Commented:
It does appear that's the case, some of the meds do not have numeric entries;

1	5	take 1 tablet by oral route  every 12 hours x 5 days
1	12	take 1 tablet (500MG)  by oral route  every 12 hours
1	4	instill 1 drop by ophthalmic route  every 4 hours into affected eye(s)
1	12	take 1 tablet by oral route  every 12 hours
3	3	take 3 milliliter by oral route  every day
apply	area(s)	apply by topical route  every day a thin layer to the affected area(s)
5	8	take 5 milliliter by oral route  every 8 hours
apply	area(s)	apply by topical route  every day a thin layer to the affected area(s)
3	3	apply by topical route 3 times every day to the affected area(s)
1	4	instill 1 drop by ophthalmic route  every 4 hours into affected eye(s)
3.5G)	(3.5	take (3.5G)  by oral route  every day
apply	area(s)	apply by topical route  every day a thin layer to the affected area(s)
4	4	apply   by ophthalmic route 4 times every day ribbon into the lower conjunctival sac(s) in the affected eye(s)
6	4	inhale 6 milliliter by inhalation route every 4 hours day via nebulizer
1	1	TAKE 1 TABLET BY MOUTH EVERY EVENING
1	(40	take 1 tablet (40MG)  by oral route  every day in the evening
1	1	take 1 tablet (100MG)  by oral route  every day as needed approximately 1 hour before sexual activity
1	2	take 1 tablet (500MG)  by oral route 2 times every day with morning and evening meals
1	1	TAKE 1 TABLET BY MOUTH EVERY EVENING
1	(40	take 1 tablet (40MG)  by oral route  every day in the evening
1	2	take 1 tablet (500MG)  by oral route 2 times every day with morning and evening meals
1	2	take 1 tablet (500MG)  by oral route 2 times every day with morning and evening meals
1	1	TAKE 1 TABLET BY MOUTH EVERY EVENING
1	(40	take 1 tablet (40MG)  by oral route  every day in the evening
1	1	TAKE 1 TABLET BY MOUTH EVERY EVENING
1	(40	take 1 tablet (40MG)  by oral route  every day in the evening
1	1	TAKE 1 TABLET BY MOUTH EVERY EVENING
1	1	TAKE 1 TABLET BY MOUTH EVERY EVENING
1	1	take 1 tablet by oral route  every day in the evening
1	1	take 1 tablet by oral route  every day in the evening
1	2	take 1 tablet (500MG)  by oral route 2 times every day with morning and evening meals
1	(40	take 1 tablet (40MG)  by oral route  every day in the evening
1	(40	take 1 tablet (40MG)  by oral route  every day in the evening
1	(40	take 1 tablet (40MG)  by oral route  every day in the evening
1	1	take 1 Tablet by oral route  every day
1	1	TAKE 1 TABLET BY MOUTH EVERY EVENING
1	1	inject 1 Vial by Injection route
1	1	TAKE 1 TABLET BY MOUTH TWICE DAILY WITH MORNING AND EVENING MEALS
1	2	take 1 tablet (500MG)  by oral route 2 times every day with morning and evening meals
1	1	TAKE 1 TABLET BY MOUTH TWICE DAILY WITH MORNING AND EVENING MEALS
1	2	take 1 capsule (300MG)  by oral route 2 times every day
1	2	take 1 capsule (300MG)  by oral route 2 times every day
1	2	take 1 capsule (300MG)  by oral route 2 times every day
1	2	take 1 capsule by oral route 2 times every day for mood
1	2	take 1 capsule (300MG)  by oral route 2 times every day
1	2	take 1 capsule by oral route 2 times every day for mood
1	2	take 1 capsule (300MG)  by oral route 2 times every day
1	2	take 1 capsule (300MG)  by oral route 2 times every day
1	2	take 1 capsule (300MG)  by oral route 2 times every day
1	2	take 1 capsule (300MG)  by oral route 2 times every day
1	2	take 1 capsule (300MG)  by oral route 2 times every day
1	2	take 1 capsule (300MG)  by oral route 2 times every day
1	1	take 1 Capsule by Oral route  every day
2	2	take 2 tablet by oral route  every day
1	(0.5	take 1 Tablet (0.5MG)  by oral route  every bedtime as needed
1	(0.5	take 1 Tablet (0.5MG)  by oral route  every bedtime as needed
1	2	take 1 tablet (6.25MG)  by oral route 2 times every day with food
1	1	take 1 tablet by oral route  every day with food
1	(50	take 1 tablet (50MG)  by oral route  every day
1	6	take 1 tablet by oral route  every 6 hours as needed for pain
1	6	take 1 tablet by oral route  every 6 hours as needed for pain
1	(40	take 1 capsule (40MG)  by oral route  every day before a meal
1	1	take 1 Tablet by oral route  every day
1	2	take 1 tablet (325MG)  by oral route 2 times every day
1	(10	take 1 tablet (10MG)  by oral route  every day at bedtime
1	2	take 1 Tablet (500MG)  by oral route 2 times every day with morning and evening meals
1	12	take 1 tablet (1000MG)  by oral route  every 12 hours
1/2	24	take 1/2 - 1 Tablet by oral route  every 8 hours as needed not to exceed 3 doses in 24 hours for for spasms
1	(37.5	take 1 tablet (37.5MG)  by oral route  every day before breakfast
1	12	take 1 tablet by oral route  every 12 hours
2G)	2	apply (2G)  by topical route 2 times every day to the affected area(s)
1	12	take 1 tablet by oral route  every 12 hours
1	6	take 1 tablet by oral route  every 6 hours as needed for pain
2MG)	7	inject (2MG)  by subcutaneous route  every 7 days once
1	(20	take 1 capsule (20MG)  by oral route  every day in the morning
1	(3	take 1 tablet (3MG)  by oral route  every bedtime as needed
1	1	take 1 tablet by oral route once
1	12	take 1 tablet by oral route  every 12 hours
2	4	take 2 tablet (500MG)  by oral route  every day for 1 day then 1 tablet (250 mg) by oral route once daily for 4 days
1	(20	take 1 capsule (20MG)  by oral route  every day in the morning
1	1	take 1 capsule by oral route  every day in the morning for mood
1	(37.5	take 1 tablet (37.5MG)  by oral route  every day before breakfast
1	1	take 1 capsule by oral route  every day before a meal
1	1	take 1 capsule by oral route  every day in the morning for mood
1	12	take 1 tablet by oral route  every 12 hours
1	(20	take 1 capsule (20MG)  by oral route  every day in the morning
2MG)	7	inject (2MG)  by subcutaneous route  every 7 days once
1	2	take 1 Tablet (25MG)  by oral route  1 - 2 times every day in the morning and evening
1/2	8	take 1/2 - 1 Tablet by oral route  every 8 hours as needed for anxiety
2	6	instill 2 drop by ophthalmic route  every 6 hours into affected eye(s)
1	2	take 1 Tablet (500MG)  by oral route 2 times every day with morning and evening meals
1	2	spray 1 - 2 spray by intranasal route  every day in each nostril as needed
1	1	take 1 capsule by oral route  every day in the morning
1	3	take 1 tablet by oral route 3 times every day prn
15	30	place 15 milliliter by mucous membrane route 3 times every day in the mouth (after meals), swish in mouth for 30 seconds then spit out
1	1	take 1 capsule by oral route  every day in the morning
1	1	take 1 capsule by oral route  every day in the morning
1/2	2	take 1/2 Tablet (250MG)  by oral route 2 times every day with morning and evening meals
1	12	take 1 tablet by oral route  every 12 hours
1	1	take 1 capsule by oral route  every day in the morning
1-2	6	take 1-2 tablet by oral route  every 6 hours as needed
1/2	6	take 1/2 - 1 Tablet (0.25MG)  by oral route  every 6 hours as needed
1	(250	take 1 tablet (250MG)  by oral route  every day
1	12	take 1 tablet by oral route  every 12 hours
1	2	take 1 Tablet (500MG)  by oral route 2 times every day with morning and evening meals
1	12	take 1 tablet by oral route  every 12 hours
1	24	take 1 Tablet by oral route  every 24 hours
1	12	take 1 tablet (500MG)  by oral route  every 12 hours
1	(20	take 1 capsule (20MG)  by oral route  every day in the morning
1/2	6	take 1/2 - 1 Tablet (0.25MG)  by oral route  every 6 hours as needed
1	12	take 1 tablet (1000MG)  by oral route  every 12 hours
1	4	instill 1 drop by ophthalmic route  every 4 hours into affected eye(s)
1	12	take 1 tablet by oral route  every 12 hours
1	(3	take 1 tablet (3MG)  by oral route  every bedtime as needed
1	3	take 1 tablet (800MG)  by oral route 3 times every day with food
1	3	take 1 tablet (10MG)  by oral route 3 times every day
1	1	TAKE 1 TABLET BY MOUTH TWO TIMES A DAY WITH FOOD
2-3	2-3	check sugars by fingerprick route 2-3 times a day for diabetes mellitus
1	2	take 1 tablet by oral route 2 times every day with morning and evening meals
1	1	TAKE 1 TABLET BY MOUTH TWICE DAILY WITH MORNING & EVENING MEALS.
1	3	check 1 by finger prick route  every 2 - 3 times for iddm
1/2	1/2	TAKE 1/2 TABLET BY MOUTH ONCE DAILY
15	6	take 15 Milliliter by mouth route  every 4 hours up to 6 doses a day as needed for diarrhea
1	1	TAKE 1 TABLET BY MOUTH TWICE DAILY WITH MORNING & EVENING MEALS.
1	1	take 1 tablet by oral route  every day
1	2	TAKE 1 TABLET BY MOUTH 2 TIMES EVERY DAY WITH MORNING AND EVENING MEALS
1	6	take 1 tablet by oral route  every 6 hours as needed
1	1	TAKE 1 TABLET BY MOUTH TWICE DAILY WITH MORNING & EVENING MEALS. MUST SEE M.D BEFORE ANY FURTHER REFILLS.
2-3	2-3	check sugars by fingerprick route 2-3 times a day for diabetes mellitus
1	2	take 1 tablet by oral route 2 times every day with morning and evening meals
1	2	TAKE 1 TABLET BY MOUTH 2 TIMES EVERY DAY WITH MORNING AND EVENING MEALS
TAKE	DAY	TAKE ONE TABLET BY MOUTH EVERY DAY
2	(200	take 2 tablet (200MG)  by oral route  every day
USE	DIRECTED	USE AS DIRECTED
1	1	take 1 tablet by oral route  every day
1	1	TAKE 1 TABLET BY MOUTH TWICE DAILY WITH MORNING & EVENING MEALS. MUST SEE M.D BEFORE ANY FURTHER REFILLS.
2-3	2-3	check sugars by finger prick route 2-3 times a day for diabetes mellitus
1	1	take 1 tablet by oral route  every day
1	4	check 1 by by finger prick route  every 1 - 4 check blood sugars up to qid for iddm
1	(320	take 1 tablet (320MG)  by oral route  every day
1	4	check 1 by by finger prick route  every 1 - 4 check blood sugars up to qid for iddm
1	1	take 1 tablet by oral route  every day
1	(60	take 1 Capsule (60MG)  by oral route  every day
1	2	TAKE 1 TABLET BY MOUTH 2 TIMES EVERY DAY WITH MORNING AND EVENING  MEALS
1	4	take 1 tablet by oral route  every 4 hours as needed for pain
1	(10	take 1 tablet (10MG)  by oral route  every day
1	4	take 1 tablet by oral route 4 times every day
1	2	take 1 tablet by oral route 2 times every day with morning and evening meals
1	2	TAKE 1 TABLET BY MOUTH 2 TIMES EVERY DAY WITH MORNING  AND EVENING  MEA LS
2-3	2-3	check blood sugar 2-3 times a day
USE	DIRECTED	USE AS DIRECTED
1	3	take 1 tablet (500MG)  by oral route 3 times every day
1	1	take 1 tablet by oral route  every day
1	2	take 1 tablet by oral route 2 times every day with morning and evening meals
1	2	TAKE 1 TABLET BY MOUTH 2 TIMES EVERY DAY WITH MORNING AND EVENING MEALS with morning and evening meals
TAKE	DAY	TAKE ONE TABLET BY MOUTH EVERY DAY
1/2	1/2	TAKE 1/2 TABLET BY MOUTH ONCE DAILY
1	(25	take 1 tablet (25MG)  by oral route  every day
take	day	take one tablet every day
1	1	take 1 tablet by oral route  every day
1	3	take 1 tablet (50MG)  by oral route 3 times every day with food
TAKE	DAY	TAKE ONE TABLET BY MOUTH EVERY DAY
TAKE	DAY	TAKE ONE TABLET BY MOUTH EVERY DAY
1	(10	take 1 tablet (10MG)  by oral route  every day
2	2	inhale 2 puff by inhalation route 2 times every day in the morning and evening
1	1	take 1 tablet by oral route  every day
1	1	take 1 tablet by oral route  every day with food
1	1	take 1 tablet by oral route  every day
1	1	take 1 tablet by oral route  every bedtime
1	1	take 1 tablet by oral route  every day
1	6	take 1 every 6 at night
1	72	apply 1 patch by transdermal route  every 72 hours
1	1	take 1 capsule by oral route  every day as needed
1	8	take 1 tablet by oral route  every 8 hours
1	1	take 1 tablet by oral route  every day
1	6	take 1 tablet by oral route  every 4 - 6 hours
2	6	inhale 2 puff by inhalation route  every 4 - 6 hours as needed
1	1	take 1 tablet by oral route  every day with the evening meal
1	2	take 1 tablet by oral route 2 times every day with food
2	6	take 2 tablet by oral route  every 6 hours as needed

Open in new window

0
CMCITDAuthor Commented:
RE; Scott.  I ran the following code;

SELECT 
    sig_desc, 
    CASE WHEN first_number_start = 0 THEN '' 
         ELSE SUBSTRING(sig_desc, first_number_start, first_number_length) 
         END AS number1,
    CASE WHEN first_number_start = 0
                    OR first_number_start =(last_number_end - last_number_length + 1)
              THEN ''
              ELSE SUBSTRING(sig_desc, last_number_end - last_number_length + 1, last_number_length)
              END AS number2
FROM patient_medication AS data
CROSS APPLY (
    SELECT PATINDEX('%[0-9]%', sig_desc) AS first_number_start,
        LEN(sig_desc) - PATINDEX('%[0-9]%', REVERSE(sig_desc)) + 1 AS last_number_end
) AS ca1
CROSS APPLY (
    SELECT PATINDEX('%[^0-9]%', SUBSTRING(sig_desc + '.', first_number_start, 100)) - 1 
        AS first_number_length,
        PATINDEX('%[^0-9]%', REVERSE(LEFT(sig_desc, last_number_end))) - 1 AS last_number_length
) AS ca2

Open in new window


And it threw the same error;

Msg 537, Level 16, State 2, Line 3
Invalid length parameter passed to the LEFT or SUBSTRING function.

Open in new window




Results:

take 2 capsule by oral route  every day	2	
take 1 tablet (500MG)  by oral route 2 times every day with food	1	2
take 1 capsule by oral route 2 times every day for 5 days	1	5
take 1 tablet by oral route 2 times every day	1	2
take 2 Tablet by oral route  every 6 hours as needed for pain	2	6
take 1 - 2 Tablet by oral route  every 4 hours as needed for pain	1	4
use as directed to check bs up to 2 times a day	2	
take 1 tablet by oral route  1 - 3 times every day as needed for muscle tightness or spasms	1	3
take 1 Capsule by oral route 2 times every day	1	2
take 1 tablet (500MG)  by oral route 3 times every day	1	3
TAKE ONE CAPSULE BY MOUTH EVERY BEDTIME		
take 2 Tablet by oral route  every day for 4 days	2	4
take 2 tablet by oral route  every day for 1 day then 1 tablet (250 mg) by oral route once daily for 4 days	2	4
take 2 tablet by oral route  every day for 1 day then 1 tablet (250 mg) by oral route once daily for 4 days for ANTIBIOTIC	2	4
take 1 capsule by oral route  every week	1	
take 1 tablet by oral route 2 times every day with food	1	2
take 1 Tablet by Oral route 2 times every day	1	2
take 1 capsule by oral route  every day in the morning	1	
take 1 tablet (500MG)  by oral route 2 times every day with food	1	2
take 1 tablet (20MG)  by oral route  every day	1	20
TAKE 1 CAPSULE DAILY	1	
TAKE 1 TABLET DAILY	1	
take 1 Tablet by Oral route  every day	1	
take 1 tablet (50MG)  by oral route  every day	1	50
take 1 Tablet by oral route  every 4 hours as needed for pain	1	4
take 1 tablet (48MG)  by oral route  every day	1	48
inhale 1 - 2 puff by inhalation route  every 4 - 6 hours as needed for cough or prior to exertion	1	6
inhale 3 milliliter by Inhalation route  every 4 hours for bronchitis, chronic	3	4
take 1 tablet (200MG)  by oral route  every day	1	200
take 1 tablet by oral route  every day	1	
TAKE 1 TO 2 TABLETS BY MOUTH EVERY SIX HOURS AS NEEDED FOR PAIN	1	2
take 1 Tablet by oral route  every day	1	
take 1 tablet by oral route daily at bedtime	1	
take 1/2 - 1 Tablet (7.5MG)  by oral route 3 times every day for for anxiety	1	3
inhale 1 - 2 puff by inhalation route  every 4 - 6 hours as needed for as needed for wheezing	1	6
take 1 tablet (37.5MG)  by oral route  every day before breakfast	1	5
take 1 tablet by oral route  every day	1	
TAKE 1 TABLET BY MOUTH EVERY DAY	1	
take 1 tablet by oral route  every day at bedtime	1	
TAKE 1 TABLET BY MOUTH EVERY SIX HOURS AS NEEDED	1	
TAKE 1 CAPSULE BY MOUTH 2 TIMES EVERY DAY	1	2
take 1 tablet by oral route  every day	1	
take 1 tablet (350MG)  by oral route  every bedtime	1	350
TAKE ONE TABLET BY MOUTH EVERY DAY		
take 1 capsule by oral route 2 times every day	1	2
		
take 1 tablet by oral route  every day	1	
TAKE 1 TABLET BY MOUTH EVERY DAY	1	
take 1 tablet by oral route 2 times every day	1	2
take 1/2 - 1 Tablet by oral route  every 6 hours as needed for pain	1	6
take 1 Tablet (10MG)  by oral route  every day	1	10
take 1 tablet (300MG)  by oral route  every day at bedtime	1	300
take 1 tablet (450MG)  by oral route 2 times every day	1	2
take 1 Tablet (10MEQ)  by oral route  every day with food	1	10
take 1 capsule by oral route  every day	1	
take 1 Tablet (20MG)  by oral route  every day	1	20
take 1 capsule by oral route  every day	1	
Use as directed		
take 1 tablet by oral route  every day	1	
take 2 tablet by oral route  every day for 1 day then 1 tablet (250 mg) by oral route once daily for 4 days	2	4
take 1 tablet (80MG)  by oral route 2 times every day	1	2
take 1 capsule (20MG)  by oral route  every day before a meal	1	20
take 1 tablet by oral route  every day	1	
take 7 milliliter by oral route  every 12 hours x 10 days	7	10
take (17G)  by oral route  every day mixed in 8 oz water per g-tube	17	8
take 1 capsule by oral route  every day	1	
take 1 tablet by oral route 3 times every day @ 7am, 5pm, and HS	1	5
take 1 capsule by oral route  every day	1	
instill 1 drop by ophthalmic route  2 times a day into affected eye(s)	1	2
take 1 tablet by oral route  every 12 hours	1	12
TAKE 1 TABLET BY ORAL ROUTE EVERY 6 HOURS AS NEEDED FOR PAIN	1	6
apply by topical route 3 times every day a small amount to the affected area	3	
take 1 tablet by oral route  1 - 3 times every day as needed for muscle tightness or spasms	1	3
take 1 Tablet (10MG)  by oral route  every day	1	10
take 2 tablet by oral route  every day as needed with food	2	
take 1 by Oral route once for DEPRESSION, ANXIETY	1	
take 1 - 2 Tablet by oral route  every 6 hours as needed for pain	1	6
chew 1 Tablet by oral route  every day in the evening	1	
TAKE ONE CAPSULE BY MOUTH EVERY DAY IN THE MORNING.		
take 1 tablet by oral route 3 times every day as needed.	1	3
TAKE ONE TABLET BY MOUTH EVERY DAY		
take 1/2 - 1 Tablet by oral route  every 8 hours as needed for anxiety/sleep	1	8
TAKE 1 TABLET BY MOUTH EVERY DAY	1	
take 1/2 - 1 Tablet by oral route  every 6 hours as needed for pain	1	6
inject by Subcutaneous route  every day		
take 1 tablet by oral route 2 times every day	1	2
take 1 tablet by oral route  every 4 hours as needed for pain	1	4
inhale 3 milliliter (2.5MG)  by nebulization route 3 times every day	3	3
TAKE 1 TABLET BY MOUTH EVERY DAY	1	
take 1 tablet (5MG)  by oral route  every day take 7.5 mg on wed. and sun and 5 on other days	1	5
TAKE 1 TABLET BY MOUTH EVERY DAY IN THE EVENING	1	
TAKE 1 TABLET BY MOUTH 3 TIMES DAILY AS NEEDED	1	3
TAKE ONE TABLET BY MOUTH TWICE DAILY WITH  MORNING  AND  EVENING  MEALS		
spray 1 spray by intranasal route 2 times every day in each nostril	1	2
take 1 tablet (10MG)  by oral route  every day	1	10
take 1 tablet by oral route 2 times every day with food	1	2
take 2 Tablet (40MG)  by oral route  every day	2	40
inhale 2 puff by inhalation route 2 times every day in the morning and evening as needed	2	2
inhale 2 milliliter (0.5MG)  by nebulization route  every day	2	5
take 1 capsule (300MG)  by oral route  every 12 hours	1	12
take 1 tablet (500MG)  by oral route 2 times every day with food	1	2
spray 2 spray by intranasal route  every day in each nostril for allergies	2	
take 2 tablet by oral route  every day for 1 day then 1 tablet (250 mg) by oral route once daily for 4 days	2	4
take 1 Tablet by oral route  every day	1	
inhale 2 puff by inhalation route  every 4 - 6 hours as needed	2	6
take 1 tablet by oral route  every day for ANXIETY, DEPRESSION, MOOD	1	
instill 1 drop by ophthalmic route  every 4 hours into affected eye(s)	1	4
take 1 tablet (2.5MG)  by oral route  every day	1	5
TAKE ONE TABLET BY MOUTH EVERY DAY		
TAKE ONE TABLET BY MOUTH ONCE DAILY		
take 1 tablet by oral route 6 hours every day as needed for pain	1	6
take 2 Tablet by oral route  every day	2	
take 2 tablet by oral route  every day	2	
take 1 tablet (500MG)  by oral route 2 times every day	1	2
take one tablet each morning 2 at bedtime	2	
take 1 tablet by oral route  every bedtime	1	
take 2 capsule by oral route  every 6 hours as needed	2	6
TAKE 1 TO 2 TABLETS BY MOUTH EVERY 4 HOURS AS NEEDED. DO NOT EXCEED 6 TABLETS/24HRS.	1	24
TAKE ONE TABLET BY MOUTH TWICE DAILY		
take 1 tablet by oral route  every bedtime	1	
take 1/2 Tablet by oral route 3 times every day as needed	1	3
take 1/2  tablet by oral route  every day for cholestrerol	1	2
take 1 Tablet by oral route  1 - 2 times every day if needed for anxiety/irritability	1	2
spray 2 spray by intranasal route  every day in each nostril	2	
take 1 tablet (40MG)  by oral route  every day	1	40
take 1 tablet by oral route  every 6 hours as needed for pain	1	6
take 5 milliliter by oral route  every 8 hours	5	8
inject 1 milliliter (150MG)  by intramuscular route  every 3 months	1	3
take 1 capsule by oral route  every 8 hours 1/2 hour before a meal or 2 hours after a meal	1	2
APPLY A SMALL AMOUNT IN MOUTH 2 TO 3 TIMES DAILY AFTER MEALS	2	3
take 1 - 2 Tablet by oral route  every 4 hours as needed for pain	1	4
take 1 tablet (10MG)  by oral route  every day	1	10
TAKE ONE TABLET BY MOUTH EVERY DAY.		
take 2 tablet by oral route  every day for 1 day then 1 tablet (250 mg) by oral route once daily for 4 days	2	4
take 1 tablet (325MG)  by oral route  every day	1	325
take 1 tablet by oral route  every 6 hours as needed	1	6
take 1 tablet (250MG)  by oral route  every day	1	250
take 1-2  tablet by oral route  every 4 hours as needed	1	4
take 1 tablet by oral route  every day	1	
take 1 tablet (250MG)  by oral route  every day	1	250
take 2 tablet (500MG)  by oral route  every day for 1 day then 1 tablet (250 mg) by oral route once daily for 4 days	2	4
take 2 tablet by oral route  every day for 1 day then 1 tablet (250 mg) by oral route once daily for 4 days	2	4
TAKE 1 TABLET BY ORAL ROUTE EVERY DAY FOR ARTHERITIS	1	
take 2 tablet by oral route  every day	2	
take 1 tablet by oral route 3 times every day	1	3
take 2 tablets by mouth every day	2	
take 2 Capsule (1000MG)  by oral route  every 12 hours for 10 days	2	10
take 1 - 2 Tablet by oral route  every 6 hours as needed for pain	1	6
take 1 capsule by oral route 2 times every day	1	2
take 1 tablet by oral route  every day	1	
take 1 tablet by oral route  every day in the evening for needs lab	1	
take 1 tablet by oral route 3 times every day with food	1	3
instill 2 drop by ophthalmic route  every 6 hours into affected eye(s)... for 4 days	2	4
TAKE ONE CAPSULE BY MOUTH EVERY DAY		
take 1 tablet (20MG)  by oral route  every day	1	20
take 1 tablet (40MG)  by oral route  every 12 hours	1	12
take 1 tablet by oral route 2 times every day	1	2
take 4 tablet by oral route  every day for 5 days then take 3 tabs PO QD for 5 days then take 2 tabs PO QD for 5 days then take 1 tab PO QD	4	1
TAKE 1 TO 2 TABLETS BY MOUTH   EVERY 6 HOURS AS NEEDED.	1	6
TAKE ONE TO TWO CAPSULES BY MOUTH THREE TIMES DAILY		
take 0.5 tablet by oral route  every day as needed approximately 1 hour before sexual activity	0	1
take 1 tablet by oral route  every day	1	
take 1 tablet (5MG)  by oral route  every day	1	5
TAKE 1 TABLET BY MOUTH EVERY DAY ON AN EMPTY STOMACH.	1	
take 1 tablet by oral route  every day for INCONTINENCE, ENURESIS	1	
take 1 tablet by oral route  every 12 hours x5 days	1	5
take 2 Tablet (2MG)  by oral route 2 times every day	2	2
take 1 Capsule by oral route 3 times daily	1	3
take 1 Tablet by oral route  every day for as  needed for swelling	1	
spray 2 spray by intranasal route  every day in each nostril	2	
TAKE 1 CAPSULE BY MOUTH EVERY DAY BEFORE A MEAL.	1	
apply by topical route 3 times every day a small amount to the affected area	3	
TAKE 1 TO 2 CAPSULES BY MOUTH AT BEDTIME AS NEEDED FOR ANXIETY, IRRITABILITY OR SLEEP	1	2
TAKE ONE TABLET BY MOUTH EVERY DAY		
take 1 tablet by oral route 3 times every day	1	3
take 1 tablet by oral route  every day	1	
take 1 tablet (25MG)  by oral route  every 6 hours as needed	1	6
take 1 tablet (10MG)  by oral route  every day	1	10
TAKE 1 TABLET BY MOUTH TWICE DAILY	1	
take 1 tablet by oral route  every day	1	
apply by topical route 2 times every day a thin layer to the affected area(s)	2	
take 1 tablet by oral route  every day	1	
TAKE ONE CAPSULE BY MOUTH EVERY DAY		
take 1 capsule (70MG)  by oral route  every day in the morning	1	70
take 1 - 2 Tablet by oral route  every 4 hours as needed for pain	1	4
take 1 tablet by oral route  every day in the morning	1	
take 1 tablet (40MG)  by oral route  every day	1	40
TAKE ONE TABLET BY MOUTH THREE TIMES DAILY WITH FOOD		
take 1 tablet (3MG)  by oral route  every day	1	3
take 1 capsule (30MG)  by oral route  every day in the morning	1	30
take 1 tablet by oral route  every day	1	
take 1 tablet by oral route  every day before breakfast	1	
take 1 tablet by oral route  every 12 hours	1	12
TAKE ONE CAPSULE BY MOUTH EVERY BEDTIME		
TAKE ONE TABLET BY MOUTH EVERY DAY		
take 1 tablet by oral route every 6 hours as needed for pain	1	6
TAKE ONE TABLET BY MOUTH EVERY DAY		
take 1 tablet by oral route  every day	1	
take 2 Tablet (2000MG)  by oral route 2 times every day for one day at onset of cold sore	2	2
take 1 tablet by oral route  every day	1	
take 1 tablet by oral route  every day for HIGH BLOOD PRESSURE	1	
wear as directed.		
take 1 tablet by oral route  every day	1	
TAKE ONE TABLET BY MOUTH TWICE DAILY WITH MEALS		
take 1 tablet by oral route  every day	1	
take 1 tablet (50MG)  by oral route  every day	1	50
take 1 tablet by oral route  every 8 hours as needed	1	8
take 1/2 - 1 Tablet by oral route 2 times every day if needed for irritability	1	2
take 1 Tablet by oral route 2 times every day	1	2
take 10 milliliter by oral route  every 12 hours for 10	10	10
take 1-2 Tablet by oral route as needed for anxiety as needed for NERVES, SLEEPLESSNESS (use rarely)	1	2
TAKE ONE TABLET BY MOUTH EVERY DAY		
take 1 tablet by oral route  every day	1	
take 1 tablet by oral route  every day	1	
apply by topical route 3 times every day for 10 days a small amount to the affected area	3	10
take 1 tablet by oral route  every day	1	
use by Subcutaneous route  every day with byetta and levimir		
take 1 tablet (10MG)  by oral route  every day	1	10
take 1 tablet (10MG)  by oral route  every bedtime	1	10
INHALE 3 MILLILITER BY NEBULIZER EVERY 4 HOURS FOR WHEEZING/SHORTNESS OF BREATH.	3	4
take 1 tablet (20MG)  by oral route  every day	1	20
take 1 by Oral route 2 times every day	1	2
take 1 tablet (50MG)  by oral route  every day	1	50
take 1 tablet (40MG)  by oral route  every day	1	40
take 1 tablet by oral route 2 times every day for 3 days	1	3
take 2 Tablet (30MG)  by oral route  every 12 hours	2	12
TAKE ONE CAPSULE BY MOUTH EVERY DAY		
inhale 2 puff by inhalation route  every day	2	
USE 1 STRIP FINGER PRICK TO CHECK BLOOD SUGARS UP TO FOUR TIMES A DAY FOR INSULIN DEPENDENT DIABETES MELLITUS	1	
take 1 tablet by oral route  every 4 hours as needed	1	4
TAKE TWO TABLETS BY MOUTH EVERY DAY AT BEDTIME		
take 1 tablet by oral route  every day	1	
TAKE 1 TABLET BY MOUTH EVERY EVENING.	1	
take 1 tablet by oral route  every day	1	
TAKE ONE CAPSULE BY MOUTH ONCE DAILY		
take (17G)  by oral route  every day	17	
take 1 tablet (1MG)  by oral route  every day	1	1
take 1 tablet (10MG)  by oral route  every day	1	10
take 1 capsule (20MG)  by oral route  every day in the morning	1	20
inject 0.5 milliliter by subcutaneous route  every week  on Tuesday in the abdomen, thigh, or upper arm rotating injection sites	0	5
take 1 tablet by oral route  every bedtime	1	
take 1 capsule by oral route  every 12 hours	1	12
take 1 tablet by oral route  every 6 hours as needed	1	6
take 1 capsule by oral route  every 6 hours	1	6
take 2 Capsule by oral route  every day	2	
TEST 2 TIMES DAILY	2	
TAKE 1 TABLET BY MOUTH ONCE A DAY	1	
TAKE ONE TABLET BY MOUTH EVERY DAY		
take 1 - 2 teaspoon by oral route  every 6 hours as needed, not to exceed 30 mL in 24 hours	1	24
take 1 capsule by oral route  every day before a meal	1	
take 1/2  tablet by oral route  with largest meal	1	2
TAKE ONE TABLET BY MOUTH ONCE DAILY		
APPLY TO AFFECTED AREA TWO TIMES A DAY FOR 2 WEEKS FOR RASH	2	
insert (1G)  by vaginal route  every week	1	
inject 10 - 50 by subcutaneous route once daily for diabetes	10	50
take 1 tablet by oral route  every day for mood stabilization	1	
take 1-2 Tablet by mouth every 4 hours as needed for pain - 5 tablets max per day	1	5
take 1 tablet by oral route  every day , in midmorning	1	
DISSOLVE 1 TABLET UNDER TONGUE EVERY 5 MINUTES FOR 3 DOSES IF NO RELIEF GO TO ER	1	3
take 1 tablet (75MCG)  by oral route  every day	1	75
take 1 capsule by oral route 3 times every day	1	3
inhale 2 puff by inhalation route  every 4 - 6 hours as needed	2	6
spray 2 spray by intranasal route  every day in each nostril	2	
take 1 tablet by oral route  every 4 hours as needed for pain	1	4
take 1 tablet by oral route 2 times every day with breakfast	1	2
take 1 tablet by oral route  every 6 hours as needed	1	6
take 1 - 2 capsule by oral route 3 times every day as needed for cough	1	3
take 1 tablet by oral route  every day for htn	1	
take 1 tablet (10MG)  by oral route  every day in the evening	1	10
take 1/2 - 1 Tablet by oral route  every 8 hours as needed for for anxiety	1	8
take 1 tablet (4MG)  by oral route  every day	1	4
MIX 17GM IN 4 TO 8 OUNCES OF WATER; JUICE OR SODA AND DRINK EVERY DAY FOR CONSTIPATION	17	8
take 1 tablet by oral route  every day at bedtime	1	
take 1/2 - 1 tablet by oral route 3 times every day as needed for pain	1	3
take 1 tablet by oral route  every day with food	1	
TAKE 1 TABLET BY MOUTH EVERY MORNING	1	
take 1 tablet (300MG)  by oral route  every day	1	300
inhale 1 - 2 puff by inhalation route 2 times every day in the morning and evening	1	2
take 1/2 - 1 Tablet by oral route  every 8 hours as needed not to exceed 3 doses in 24 hours	1	24
take 2 tablet by oral route  every day	2	
take 1 tablet by oral route  every 12 hours	1	12
take 1 tablet (10MG)  by oral route 2 times every day before a meal	1	2
take 1 tablet (250MG)  by oral route  every 12 hours	1	12
take 1 tablet by oral route 2 times every day	1	2
take 2 Tablet (625MG)  by oral route 2 times every day with a meal and liquid	2	2
AS DIRECTED WITH  LEVEMIR		
inhale 1 capsule by inhalation route  every day using 2 inhalations via handihaler	1	2
take 1 capsule by oral route  every day 1/2 hour following the same meal each day	1	2
take 12 milliliter by oral route  every 12 hours	12	12
take 1/2 (2MG)  by oral route  every 8 hours as needed for nausea	1	8
take 1 capsule by oral route 3 times every day for 10 days	1	10
take 1 Tablet (1MG)  by oral route 3 times every day	1	3
TAKE ONE TABLET BY MOUTH EVERY DAY		
inhale 1 - 2 puff by inhalation route 2 times every day in the morning and evening	1	2
take 1 tablet by oral route  every day for high chol	1	
take 1 tablet by oral route  every day	1	
take 1.5 Tablet by oral route  every day	1	5
take 5 milliliter by oral route  every 8 hours	5	8
apply by topical route  every day a thin layer to the affected area(s)		
take 3 tablet by oral route  every bedtime just at hs	3	
take 1 capsule (60MG)  by oral route  every day in the morning	1	60
take 1 Tablet (5MG)  by oral route  every 6 hours as needed for pain	1	6
TAKE 1 TABLET BY MOUTH EVERY DAY FOR CHOLESTEROL.  NEED FASTING LAB BEFORE DONE	1	
TAKE 1 CAPSULE EVERY DAY  BEFORE  A  MEAL	1	
take 2 Tablet (200MG)  by oral route  every day	2	200
take 1 tablet by oral route  every day	1	
take 1 tab by oral route  every day	1	
take 1 tablet by oral route  every 4 hours as needed for pain	1	4
take 10 milliliter by oral route  every 12 hours for 10	10	10
TAKE ONE TABLET BY MOUTH TWICE DAILY		
take 1 - 2 tablet by oral route  every bedtime for ANXIETY, SLEEP	1	2
take 1 tablet (2.5MG)  by oral route  every day before breakfast	1	5
take 1 capsule by oral route  every 6 hours	1	6
TAKE 1 TABLET BY MOUTH EVERY FOUR HOURS AS NEEDED FOR PAIN **MUST LAST 30 DAYS**	1	30
take 1 tablet (1MG)  by oral route  every day	1	1
take 1 Tablet by oral route  every bedtime	1	
take 1 capsule (50MG)  by oral route  every bedtime	1	50
take 2 Tablet (200MG)  by oral route  every day	2	200
take 1 tablet (500MG)  by oral route 2 times every day with morning and evening meals	1	2
Use as directed		
take 5mg by oral route  every 2 hours as needed for pain/sob ; take 5 mg by mouth at bedtime	5	5
take 1 capsule (40MG)  by oral route  every day	1	40
TAKE 1 TABLET DAILY	1	
take 1 tablet (145MG)  by oral route  every day	1	145
take 1 tablet (100MCG)  by oral route  every day	1	100
take 1 tablet by oral route  every day	1	
take 2  capsule (300MG)  by oral route TID	2	300
take 1 tablet by oral route  every 3rd day	1	3
take 1 tablet by oral route  every day	1	
TAKE 1 TO 2 TABLET BY MOUTH EVERY 6 HOURS AS NEEDED FOR PAIN AS NEEDED for Migraine Pain	1	6
take 5 milliliter by oral route  every 12 hours	5	12
TAKE ONE TABLET BY MOUTH EVERY DAY IN THE MORNING.		
take 1 tablet by oral route 2 times every day for 2 weeks then stop	1	2
take 1 tablet (15MG)  by oral route  every day	1	15
take 1 tablet (40MG)  by oral route  every day in the evening	1	40
instill by otic route 3 times every day into affected ear(s) enough drops to fill ear canal	3	
TAKE 1 TABLET BY MOUTH THREE TIMES DAILY AS NEEDED	1	
take 1 tablet by oral route  every day at bedtime	1	
TAKE 1 TABLET BY MOUTH EVERY DAY	1	
take 3 capsule by oral route  every day	3	
take 1/2 - 1 Tablet (2.5MG)  by oral route 3 times every day	1	3
TAKE 1/2 T0 1 TABLET BY MOUTH ONCE DAILY	1	1
TAKE ONE TABLET BY MOUTH TWICE DAILY		
place 15 milliliter by mucous membrane route 3 times every day in the mouth (after meals), swish in mouth for 30 seconds then spit out	15	30
take 1 tablet by oral route 2 times every day as needed	1	2
TAKE 1 TABLET BY MOUTH EVERY DAY IN THE EVENING FOR CHOLESTEROL	1	
take 5 milliliter by oral route  every 12 hours	5	12
TAKE ONE TABLET BY MOUTH ONCE DAILY		
take 1 tablet (10MG)  by oral route 2 times every day	1	2
TAKE 1 TO 2 TABLETS BY MOUTH EVRY 6 HOURS AS NEEDED FOR PAIN AND DISCOMFORT	1	6
TAKE ONE TABLET BY MOUTH EVERY DAY		
take 1 tablet by oral route  every day	1	
take 1 capsule (300MG)  by oral route 4 times every day	1	4
take 1 capsule by oral route  every day with food in the AFTERNOON for MOOD	1	
take 2 tablet (500MG)  by oral route  every day for 1 day then 1 tablet (250 mg) by oral route once daily for 4 days	2	4
TAKE ONE TABLET BY MOUTH EVERY DAY		
take (17G)  by oral route  every day mixed with 8 oz. water, juice, soda, coffee or tea	17	8
take 5 milliliter by oral route  every 12 hours	5	12
take 1 tablet (37.5MG)  by oral route  every day before breakfast	1	5
TAKE 1 TO 2 TABLETS BY MOUTH    EVERY 6 HOURS AS NEEDED. MUST LAST 8 DA Y S  PER INSURANCE & M.D.	1	8
Use to check blood sugars once every day		
spray 1 - 2 spray by intranasal route  every day in each nostril	1	2
TAKE 1/2 TO 1 TABLET BY MOUTH EVERY 8 HOURS AS NEEDED FOR ANXIETY AND TREMORS	1	8
inhale 2 puff by inhalation route  every day	2	
TAKE 1 TABLET BY MOUTH TWICE DAILY	1	
take 1 tablet by oral route  every 6 hours as needed for pain	1	6
apply by topical route 2 times every day a thin layer to the affected area(s)	2	
take 1 tablet (30MG)  by oral route  every 12 hours	1	12
give 2 ml po every day for 1 month	2	1
TAKE 1 CAPSULE BY MOUTH EVERY EVENING	1	
TAKE 1 TABLET THREE TIMES DAILY AS NEEDED	1	
take 0.5 Teaspoon by Oral route  every bedtime for ALLERGIES	0	5
spray 2 spray by intranasal route  every day in each nostril	2	
TAKE 1 CAPSULE BY MOUTH EVERY DAY IN THE MORNING	1	
take 1 tablet (50MG)  by oral route 2 times every day	1	2
take 1 tablet (20MG)  by oral route 2 times every day	1	2
take 1 tablet by oral route  every day	1	
take as needed for migraines maximum of 4 per day	4	
TAKE 1 TO 2 TABLETS BY MOUTH EVERY FOUR HOURS AS NEEDED FOR PAIN	1	2
take 1 tablet (37.5MG)  by oral route  every day before breakfast	1	5
take 1 tablet (25MCG)  by oral route  every day	1	25
take 1/2 - 1 Tablet (5MG)  by Rectal route  every 6 hours for seizures	1	6
take 1 tablet (150MG)  by oral route once and repeat in 72 hours if needed	1	72
take 1 tablet by oral route  every day	1	
TAKE 1 TABLET BY MOUTH EVERY DAY	1	
take 1 tablet (10MG)  by oral route  every day	1	10
instill 4 drops by Otic route 4 times every day for 7 days into the left	4	7
take 2 tablet by oral route  every day for 1 day then 1 tablet (250 mg) by oral route once daily for 4 days	2	4
take 1 tablet by oral route  every bedtime	1	
apply (0.5G)  by topical route 3 times every week	0	3
take 1 tablet by oral route  every 4 - 6 hours as needed for pain	1	6
TAKE 1 TABLET BY MOUTH EVERY MORNING for MOOD	1	
take 1 capsule by oral route  every day in the morning	1	
apply by topical route  every day to the affected and surrounding areas of skin		
inhale 2 puff by inhalation route  every 4 - 6 hours as needed	2	6
take 1 capsule by oral route 3 times every day	1	3
TAKE 1 TABLET BY MOUTH EVERY MORNING FOR BEHAVIOR	1	
take 1 tablet by oral route  every day	1	
TAKE ONE TABLET BY MOUTH EVERY EIGHT HOURS AS NEEDED		
Mix contents of 1 cap with apple juice, give via g-tube once daily	1	
take 1/2 - 1 Tablet by oral route  every 8 hours as needed not to exceed 3 doses in 24 hours for spasms	1	24
take 2 tablet by oral route  every day for 1 day then 1 tablet (250 mg) by oral route once daily for 4 days for ANTIBIOTIC	2	4
take 1 Tablet by oral route  every 4 hours as needed for pain	1	4
inhale 2 puff by inhalation route  every 4 - 6 hours as needed	2	6
take 1 tablet by oral route once every 3 days	1	3
apply by topical route 2 times every day to the affected area(s)	2	
take 3 capsule by oral route  every day	3	
take 1 Tablet by oral route  every 4 hours as needed for pain	1	4
take 1 tablet by oral route  every day in the evening	1	
take 1 tablet (10MG)  by oral route  every day	1	10
TAKE 1/2 TO 1 TABLET BY MOUTH ONCE EVERY DAY AS NEEDED. USE SPARINGLY.	1	1
take 1 tablet (10MG)  by oral route  every day at bedtime	1	10
take 1 tablet (40MG)  by oral route  every day in the evening	1	40
spray 1 - 2 spray by intranasal route  every day in each nostril	1	2
take 1 tablet by oral route  every day	1	
TAKE 1 TABLET BY MOUTH AT BEDTIME	1	
chew 1 Tablet by oral route  every day in the evening	1	
take 1 Tablet (10MG)  by oral route  every day	1	10
take 1 tablet by oral route  every 6 hours as needed for pain	1	6
inject by subcut 3 times day per sliding scale 	3	
take 0.5 tablet by oral route 2 times every day with meals	0	2
instill 1 drop by ophthalmic route 3 times every day into affected eye(s) for x7	1	7
TAKE 1 TABLET BY MOUTH EVERY 6 HOURS AS NEEDED FOR PAIN	1	6
take 2 tablet (500MG)  by oral route  every day for 1 day then 1 tablet (250 mg) by oral route once daily for 4 days	2	4
take 1 tablet by oral route  every 12 hours for 10 days	1	10
TAKE 1/2 TABLET BY MOUTH EVERY DAY **GENERIC CARDURA**	1	2
take 1 tablet by oral route  every 6 hours as needed for pain	1	6
take 1 tablet (500MG)  by oral route 2 times every day with food	1	2
take 1 capsule by oral route  every day in the morning	1	
take 1 capsule (20MG)  by oral route  every day	1	20
take 1 capsule by oral route  every day	1	
take 1 tablet by oral route  every day with food	1	
take 1 - 2 Tablet by oral route  every 4 hours as needed for pain	1	4
take 1/2 - 1 Tablet by oral route  every 6 hours as needed for pain	1	6
take 1 Tablet (500MG)  by oral route  every 24 hours	1	24
take 1 tablet by oral route  every bedtime in the evening	1	
take 1 tablet by oral route  every day	1	
TAKE 1 TABLET BY MOUTH EVERY  4 HOURS AS NEEDED	1	4
take 1/2 - 1 Tablet (0.25MG)  by oral route  every 8 hours for agitation as needed	1	8
take 1 tablet by oral route  every day swallowing whole with water. Do not crush, chew and/or divide.	1	
TAKE ONE TABLET BY MOUTH EVERY DAY		
take 5 milliliter by oral route  every 12 hours	5	12
take 1-2 capsule by oral route 3 times every day for NERVE PAIN AT BEDTIME	1	3
take 1 tablet (500MG)  by oral route 2 times every day with morning and evening meals	1	2
take 1 capsule (500MG)  by oral route 4 times every day	1	4
take 1 tablet (500MG)  by oral route  every morning	1	500
take 1 tablet (325MG)  by oral route  every day	1	325
take 1 tablet (30MG)  by oral route  every day	1	30

Open in new window

0
Bill PrewCommented:
It looks like this test case will throw that error...

'1 tab daily'


»bp
0
Scott PletcherSenior DBACommented:
Interesting.  Please make the changes underlined below (adding . to strings) and try again:

CROSS APPLY (
    SELECT PATINDEX('%[0-9]%', string) AS first_number_start,
        LEN(string) - PATINDEX('%[0-9]%', REVERSE(string) + '.') + 1 AS last_number_end
) AS ca1
CROSS APPLY (
    SELECT PATINDEX('%[^0-9]%', SUBSTRING(string + '.', first_number_start, 100)) - 1
        AS first_number_length,
        PATINDEX('%[^0-9]%', REVERSE(LEFT(string, last_number_end)) + '.') - 1 AS last_number_length
) AS ca2
0
Bill PrewCommented:
Let me preface by saying I'm not trying to shoot holes in any solutions (mine certainly didn't handle all test cases), so I've switched to adding a little value as a tester to see if we can perfect Scott's approach.

Not sure if these can occur in the data, but it looks like they will need additional consideration to work properly as well:

    ('take .1 tablet by oral route every 4 hours as needed'),
    ('take 1 tablet by oral route every .4 hours as needed'),
    ('take 1/2 tablet by oral route every 2/3 hours as needed')


»bp
0
Scott PletcherSenior DBACommented:
Bill:

No issues at all, I appreciate the help trying to find the "bad data" pattern that is causing the problem.

I tried to be careful constructing the code, but looking it back over, the only thing I saw that could cause the error was in the "last_number_length" calc, and I don't really understand even how that happens, so I'm hoping adding the " + '.' " takes care of that.  

With any luck, OP can find an actual data entry or two that is/are causing the problem.
0
Scott PletcherSenior DBACommented:
Ooh wait, let me make a different change:

SELECT
    string,
    CASE WHEN first_number_start = 0 THEN ''
         ELSE SUBSTRING(string, first_number_start, first_number_length)
         END AS number1,
    CASE WHEN first_number_start = 0 OR last_number_end < 3
                    OR first_number_start = (last_number_end - last_number_length + 1)
              THEN ''
              ELSE SUBSTRING(string, last_number_end - last_number_length + 1, last_number_length)
              END AS number2
FROM dbo.table_name
CROSS APPLY (
    SELECT PATINDEX('%[0-9]%', string) AS first_number_start,
        LEN(string) - PATINDEX('%[0-9]%', REVERSE(string)) + 1 AS last_number_end
) AS ca1
CROSS APPLY (
    SELECT PATINDEX('%[^0-9]%', SUBSTRING(string + '.', first_number_start, 100)) - 1
        AS first_number_length,
        PATINDEX('%[^0-9]%', REVERSE(LEFT(string, last_number_end))) - 1 AS last_number_length
) AS ca2
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.