>>>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
>>
>>If the amount following the drag name always starts with a number, then it's easy enough. Search for the first digit after in the Drag Name.
>
>and before [-] :-)

Sure.

In SQL 2005+ it's easy to solve with CROSS APPLY, in SQL 2000 we need to use derived tables.