I have a column of drug names with dose and I’m trying to extract the just the dose from the field. The difficulty comes in when I have combination drugs that have multiple doses. I can either extract the first numbers in the string, or all of them in one string with no way to separate them.
String 1: CARBIDOPA 48.75MG/LEVODOPA 195MG SA CAP String 2: BUPRENORPHINE 8MG/NALOXONE 2MG SL TAB String 3: HCTZ 12.5MG/LOSARTAN 100MG TAB
Desired output
String 1: 48.75MG/195MG String 2: 8MG/2MG String 3: 12.5MG/100MG
I could deal without the MG in the output, or even have the output be two different columns, but there are cases where drugs have three ingredients.
Advertisement
Answer
This works fine.
This is the hard cording solution. Use it wisely.
CREATE TABLE TEST (STRING VARCHAR(MAX))
INSERT INTO TEST VALUES
('CARBIDOPA 48.75MG/LEVODOPA 195MG SA CAP'),
('BUPRENORPHINE 8MG/NALOXONE 2MG SL TAB'),
('HCTZ 12.5MG/LOSARTAN 100MG TAB')
SELECT SUBSTRING(STRING, PATINDEX('%[0-9]%',STRING), CHARINDEX('/',STRING)-PATINDEX('%[0-9]%',STRING) + 1) --BEGIN WITH NUMBER & END WITH '/'
+
SUBSTRING(
SUBSTRING(STRING,CHARINDEX('/',STRING),LEN(STRING) - CHARINDEX('/',STRING) + 1), --RIGHT STRING
PATINDEX('%[0-9]%',SUBSTRING(STRING,CHARINDEX('/',STRING),LEN(STRING) - CHARINDEX('/',STRING) + 1)), --START POINT OF RIGHT STRING
CHARINDEX(' ',SUBSTRING(STRING,CHARINDEX('/',STRING),LEN(STRING) - CHARINDEX('/',STRING) + 1),CHARINDEX(' ',SUBSTRING(STRING,CHARINDEX('/',STRING),LEN(STRING) - CHARINDEX('/',STRING) + 1)) + 1) - PATINDEX('%[0-9]%',SUBSTRING(STRING,CHARINDEX('/',STRING),LEN(STRING) - CHARINDEX('/',STRING) + 1)) --LENGTH
)
FROM TEST
Test Result