We help IT Professionals succeed at work.

Negative isnull?

229 Views
Last Modified: 2017-03-12
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] 
	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

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?
Comment
Watch Question

CERTIFIED EXPERT

Commented:
you need to include ltrim and rtrim functions into ur sp
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Nigel Keith-WalkerContractor

Author

Commented:
LTRIM worked fine in my stored proc.