Display part of entry

Question

Suppose a query "select streetAdr from Address" returns "236 a1 road" "333 a2 road" and 444 a4 road" as 3 rows. How i can display only "236" "333" and "444" in SQL Server.

Answer 1

Try:

Select left(yourcolumn, charindex(' ',yourcolumn)) ...

Answer 2

Just to be on the safe side, if any of your addresses should only have a number and nothing else:

declare @Address table (AddressLine1 nvarchar(50) NOT NULL)
insert into @Address values ('236 a1 road')
insert into @Address values ('333 a2 road')
insert into @Address values ('444 a4 road')
insert into @Address values ('555')
select
   CASE
      WHEN charindex(' ', AddressLine1) > 0 THEN
         Left(AddressLine1, charindex(' ', AddressLine1))
      ELSE
         AddressLine1
   END AS AddressLine1
from @Address