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.
x
String 1: CARBIDOPA 48.75MG/LEVODOPA 195MG SA CAPString 2: BUPRENORPHINE 8MG/NALOXONE 2MG SL TABString 3: HCTZ 12.5MG/LOSARTAN 100MG TABDesired output
String 1: 48.75MG/195MGString 2: 8MG/2MGString 3: 12.5MG/100MGI 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 STRINGPATINDEX('%[0-9]%',SUBSTRING(STRING,CHARINDEX('/',STRING),LEN(STRING) - CHARINDEX('/',STRING) + 1)), --START POINT OF RIGHT STRINGCHARINDEX(' ',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 TESTTest Result