UFN_ADDRESS_CLEAN

            CREATE function dbo.UFN_ADDRESS_CLEAN(
                @ADDRESS nvarchar(250)
            )
            returns nvarchar(250)
            with execute as caller
            as begin

                declare @NEWLINE nvarchar(2) = NCHAR(13)+NCHAR(10)

                declare @CLEANADDRESS nvarchar(250) = ''
                declare @CURRENTLINE nvarchar(250)
                declare @DELIM int

                --For each line in the address...

                while (len(@ADDRESS) > 0)
                begin
                    --Find the end of the line, either at the first line break or end of the address.

                    set @DELIM = charindex(@NEWLINE,@ADDRESS)-1;
                    if @DELIM < 0
                        set @DELIM = len(@ADDRESS)

                    --Remove any white space around or special characters at the beginning of each line.

                    set @CURRENTLINE = ltrim(rtrim(substring(@ADDRESS,1,@DELIM)));
                    while (unicode(left(@CURRENTLINE,1)) in (44,45,46,47,58)) --First character is a comma, dash, period, slash, or colon

                    begin
                        --Remove the first character and any whitespace following it.

                        set @CURRENTLINE = ltrim(substring(@CURRENTLINE,2,len(@CURRENTLINE)-1))
                    end

                    --Only include the line if, after cleaning, it isn't blank.

                    if len(@CURRENTLINE) > 0
                    begin
                        set @CLEANADDRESS = @CLEANADDRESS + @CURRENTLINE + @NEWLINE
                    end

                    --Trim line off the raw address and repeat.

                    set @ADDRESS = substring(@ADDRESS,@DELIM+3,len(@ADDRESS))
                end

                if len(@CLEANADDRESS) > 0
                    set @CLEANADDRESS = left(@CLEANADDRESS,len(@CLEANADDRESS)-len(@NEWLINE))

                return @CLEANADDRESS
            end