@test-2='Barton STREET'

select ltrim(rtrim(reverse(left(reverse(@test),charindex(' ',reverse(@test))))))

here @test-2 is your field.

I just wrote the logic. You can modify the result accordingly.

note: this logic works even if you have space in street name (like...

You can create a Temp table (as per your result set) and store the result from cusror into temp table. Select from temp table.

Another way is

SELECT

CASE WHEN col2 IS NULL THEN col1

ELSE col1+ ', ' +col2 END

FROM ....