Some of the addresses have null in street number. This means that the full address commences with a space.
ALTER PROCEDURE [dbo].[sp_Name_Addr] AS SELECT (isnull([FirstName],'') +' '+ ISNULL([LastName],'')) as Fullname, (ISNULL([StreetNumber],'') + ' ' + ISNULL([Address],'')+','+ ISNULL([Suburb],'')+','+ISNULL( [PCode],'')+','+ ISNULL([State],'')+','+ ISNULL([Country],'')) as FullAddr,[ID], [Status] FROM [tblCustomer] Where Firstname is Not null and Status <> 'Expired' ORDER BY Fullname
Do more with