I don't think that will actually work in all situations.

with p as

(

select 'asdf1234' as Delivery_Site union all

select 'asdf 1234' union all

select ' asdf asdf ' union all

select ''

)

select

LTRIM(LEFT(p.Delivery_Site, CHARINDEX(' ',p.Delivery_Site))) --This misses too many things

, LTRIM(LEFT(p.Delivery_Site, LEN(p.Delivery_Site) - CHARINDEX(' ', LTRIM(p.Delivery_Site)))) --This captures the first word

from p

Take a look at this. The first column is what you posted. It returns an empty string for everything except the second value.

--edit--

testing an edit for Steve since somebody reported a bug when editing a post.