A simple solution

😎

with p as

(

select 'asdf1234' as Delivery_Site union all

select 'asdf 1234' union all

select ' asdf asdf ' union all

select ''

)

,TXT_INFO AS

(

SELECT

LTRIM(PP.Delivery_Site) AS Delivery_Site

,CHARINDEX(' ',PP.Delivery_Site) AS HAS_SPACE

,LEN(LTRIM(PP.Delivery_Site)) AS HAS_CHAR

FROM p PP

)

SELECT

CASE

WHEN TI.HAS_CHAR > 0 AND TI.HAS_SPACE > 0 THEN SUBSTRING(TI.Delivery_Site,1,CHARINDEX(' ',TI.Delivery_Site))

WHEN TI.HAS_CHAR > 0 AND TI.HAS_SPACE = 0 THEN TI.Delivery_Site

ELSE NULL

END AS FIRST_WORD

FROM TXT_INFO TI;

Results

FIRST_WORD

-----------

asdf1234

asdf

asdf

NULL