Suppose the column name of table is mValue and tablename is xy then following query will give the result:

select mvalue,RequiredValue = case when ISNUMERIC(left(mvalue,1))=1 then SUBSTRING(mvalue,1,CHARINDEX(' ',mvalue)-1)

when ISNUMERIC(right(mvalue,1))=1 then right(mvalue,CHARINDEX(' ',reverse(mvalue))-1)

else '' end from xy