Concatenate address fields for display

				
--**************************************
-- Name: Concatenate address fields for display
-- Description:When displaying address information from multiple fields in your database generally each field is separated by a carriage return line feed. If one of the values is null or an empty field then you don’t want to display the empty lines. This little function ignores empty lines but adds the CRLF on to other address lines.
-- By: Jonathan Spinks
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=1096&lngWId=5--for details.--**************************************

IF EXISTS (select * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[fnAddCR]') AND xtype in (N'FN', N'IF', N'TF'))
	DROP function [dbo].[fnAddCR]
GO
create function dbo.fnAddCR(@sIn varchar(150)) 
	returns varchar(150) 
/*
Developer:	Jonathan Spinks
Date: 13/09/06
Description: Concatenates address fields with CRLF's for display purposes
Inputs: 
	@sIn = the string that if not null or empty an CRLF is added to
Return:
	the input string with added CRLF or an empty string (note nulls are converted into empty strings)
Example usage:
Select dbo.fnAddCR('Address1')+dbo.fnAddCR('Address2')+dbo.fnAddCR('Address3')+dbo.fnAddCR('City')+dbo.fnAddCR('County')
returns:
Address1
Address2
Address3
City
County
Select dbo.fnAddCR('Address1')+dbo.fnAddCR('')+dbo.fnAddCR('Address3')+dbo.fnAddCR(null)+dbo.fnAddCR('County')
returns:
Address1
Address3
County
Copyright © 2006 Programming. Heathmill
*/
 
Begin
	return isnull(nullif(@sIn+char(13)+char(10),char(13)+char(10)),'')
end
GO