My Technical Notes

Wednesday, 4 February 2015

Address Lines, the Trimming and Concatenating of

The below function trims and concatenates four address lines:


IF OBJECT_ID (N'dbo.ConcatAddressLines', N'FN') IS NOT NULL
    DROP FUNCTION ConcatAddressLines;
GO

CREATE FUNCTION dbo.ConcatAddressLines
(
    @addressLine1 NVARCHAR(MAX),
    @addressLine2 NVARCHAR(MAX),
    @addressLine3 NVARCHAR(MAX),
    @addressLine4 NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    -- store the lines in a table
    DECLARE @lines TABLE (line NVARCHAR(MAX))
    INSERT INTO @lines (line)
    VALUES (@addressLine1), (@addressLine2), (@addressLine3), (@addressLine4)
    
    -- trim the lines
    UPDATE @lines
    SET line = COALESCE(LTRIM(RTRIM(line)), '')
    
    DECLARE @ret NVARCHAR(MAX)
    
    SELECT @ret = COALESCE(@ret + ', ', '') + LTRIM(RTRIM(line))
    FROM @lines
    WHERE line <> ''
    
    RETURN @ret
END
GO

Note that there is no such thing as an optional argument so you will have to supply all four. You can supply `NULL` instead of an argument if you wish to miss them out.

No comments: