Negative isnull?

Nigel Keith-Walker
Nigel Keith-Walker used Ask the Experts™
I have a stored procedure that selects details from a customer file to build a full name and full address.
The select statement interposes spaces and commas into the full address.
ALTER  PROCEDURE [dbo].[sp_Name_Addr] 
(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

Open in new window

Some of the addresses have null in street number.  This means that the full address commences with a space.  
Tried to put NOT isnull(streetNumber,' ') into the select statement but the NOT is not accepted.
Any suggestions?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

you need to include ltrim and rtrim functions into ur sp
Software Team Lead
for example :

select ltrim(rtrim(isnull([FirstName],'') +' '+      ISNULL([LastName],''))) as Fullname
Nigel Keith-WalkerContractor


LTRIM worked fine in my stored proc.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial