Ha! Sean, you elegantly rewrote my function (stollen from somwhere anyway).

I do not think that to use cte in my case is a good idea - I use this function within a select statement from a big table - not sure that cte can be used here

SELECT

Real_Date_Time ChartedAt

,DRUG_INFO_1

,LEFT(drug_info_1,CHARINDEX(CHAR(32),drug_info_1)) Drug

,dbo.fnExtractDigits(DRUG_INFO_1) as DoseOrFirstNumeric

, Coalesce(case charindex('MG', DRUG_INFO_1)

when 0 then null

else 'MG'

end --Mg

,case charindex('GM', DRUG_INFO_1)

when 0 then null

else 'GM'

end --GM

,case charindex('UNIT', DRUG_INFO_1)

when 0 then null

else 'UNITS'

end ----UNITS

)

,ROUTE

,NO_OF_INGREDIENTS

,PMP

,pat_num

,ADMIN_STATUS

FROM MAK_Data.dbo.CHRT_Tran

Where

LEFT(drug_info_1,CHARINDEX(CHAR(32),drug_info_1))

IN

(

'abacavir', 'acyclovir', 'amikacin', 'amoxicillin', 'amoxicillin-clavulanic', 'amphotericin', 'ampicillin',

'ampicillin-sulbactam', 'atazanavir', 'atovaquone', 'azithromycin', 'aztreonam',

'cefazolin', 'cefdinir', 'cefepime', 'cefixime', 'cefotaxime', 'cefotetan',

'cefoxitin', 'cefpodoxime proxetil', 'ceftazidime', 'ceftriaxone', 'cefuroxime',

'cefuroxime axetil', 'cephalexin', 'ciprofloxacin', 'clarithromycin', 'clindamycin',

'colistimethate', 'dapsone', 'daptomycin', 'demeclocycline', 'dicloxacillin', 'doxycycline',

'efavirenz', 'ertapenem', 'erythromycin', 'erythromycin', 'ethambutol', 'fluconazole', 'gentamicin',

'hydroxychloroquine', 'imipenem', 'isoniazid', 'itraconazole', 'ketoconazole', 'lamivudine', 'levofloxacin',

'linezolid', 'meropenem', 'methenamine', 'metronidazole', 'minocycline', 'nafcillin',

'neomycin', 'nevirapine', 'nitrofurantoin', 'nystatin', 'oseltamivir', '

penicillin', 'pentamidine', 'piperacillin-tazobactam (single)', 'pyrazinamide', 'ribavirin',

'rifabutin', 'rifampin', 'ritonavir', 'sulfamethoxazole/trimethoprim', 'sulfasalazine',

'tenofovir', 'terbinafine', 'tetracycline', 'tigecycline', 'tobramycin', 'trimethoprim',

'valacyclovir', 'vancomycin', 'voriconazole'

)

and ROUTE IN ('iv','PO')

AND Real_Date_Time >'6/1/2013'

and ADMIN_STATUS ='admin'