>VANCOMYCIN 1 GM - Route: INTRAVENOUS @ 08:45 By: GAM >ANCEF - Route: INTERVENOUS @ 11:15 BY GAM >ANCEF 2 GRAMS - Route: INTRAVENOUS @ 09:25 By: DR. HORNUNG >>
select case when PATINDEX('% [0-9]%', DragName)>0 then LEFT(DragName,PATINDEX('% [0-9]%', DragName)) else DragName end as DragName, [Route], [Time], [Give By] from (select SUBSTRING(Drag, 1, charindex(' - Route: ',Drag)) as DragName, SUBSTRING(Drag, charindex(' - Route: ',Drag) + 10, CHARINDEX(' @ ', Drag)- charindex(' - Route: ',Drag)-10) as [Route], SUBSTRING(Drag, CHARINDEX(' @ ', Drag) + 3, CHARINDEX(' By',Drag) -CHARINDEX(' @ ', Drag) - 3) as [Time], SUBSTRING(Drag, CHARINDEX(' By',Drag) + 4, LEN(Drag)) as [Give By] from @Drags) XThe code can be simplified in SQL 2005+