>I am trying to create a case statement in t-sql to extract the various parts of this field into separate elements
>
>Sample Data
>

>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
>

>
>Basically, I need 4 separate elements, so for the first one it would be
>Drug Name: VANCOMYCIN
>Route: INTRAVENOUS
>Time: 08:45
>Give By: GAM
>
>The only logic I can't really grasp is getting the Drug Name without the amount. The rest I have definable points I can utilize to include with the CHARINDEX feature. But since some of the drug names have an amount and some don't I'm stumped on that part.
>
>Thanks for any help

Try

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) X

The code can be simplified in SQL 2005+