UFN_ADDRESS_ADDRESSBLOCK_NORMALIZE_LOWERCASE

Function returns a normalized address block in lowercase. Helpful when comparing addresses.
Return

Return Type
nvarchar(150)
Parameters

Parameter	Parameter Type	Mode	Description
@ADDRESS	nvarchar(150)	IN
@SUBSTITUTIONS	UDT_STRINGPAIR_100	IN
Definition

Copy


            CREATE function dbo.UFN_ADDRESS_ADDRESSBLOCK_NORMALIZE_LOWERCASE(
                    @ADDRESS nvarchar(150),
                    @SUBSTITUTIONS dbo.UDT_STRINGPAIR_100 READONLY
            )
            returns nvarchar(150)
            with execute as caller
            as begin
                set @ADDRESS = ' ' + lower(isnull(@ADDRESS,'')) + ' ';

                --Removing special characters

                select @ADDRESS = replace(@ADDRESS,'*','')
                select @ADDRESS = replace(@ADDRESS,'.','')
                select @ADDRESS = replace(@ADDRESS,',','')
                select @ADDRESS = replace(replace(@ADDRESS,'(',''),')','')
                select @ADDRESS = replace(@ADDRESS,'"','')
                select @ADDRESS = replace(@ADDRESS,';','')
                select @ADDRESS = replace(@ADDRESS,':','')
                select @ADDRESS = replace(@ADDRESS,'''','')
                select @ADDRESS = replace(@ADDRESS,'-','')
                select @ADDRESS = replace(@ADDRESS,'@','')
                select @ADDRESS = replace(@ADDRESS,'&','')

                --Removing whitespace

                select @ADDRESS = replace(replace(replace(@ADDRESS, char(13), ' '), char(10), ' '), char(9), ' ')

                declare @REMOVEDWHITESPACE bit = 0
                declare @TRIMMEDADDRESS nvarchar(150)
                while @REMOVEDWHITESPACE = 0
                begin
                    set @TRIMMEDADDRESS = replace(@ADDRESS, '  ', ' ')
                    if @TRIMMEDADDRESS <> @ADDRESS
                        set @ADDRESS = @TRIMMEDADDRESS
                    else
                        set @REMOVEDWHITESPACE = 1
                end

                if exists(select top 1 1 from @SUBSTITUTIONS)
                begin
                    select @ADDRESS = replace(@ADDRESS, ' ' + [STRING1] + ' ', ' ' + [STRING2] + ' ')
                    from @SUBSTITUTIONS
                end
                else
                begin
                    declare @ADDRESSSUBSTITUTIONS dbo.UDT_STRINGPAIR_100

                    insert into @ADDRESSSUBSTITUTIONS
                    select 
                        [DESIGNATION],
                        [ABBREVIATION]
                    from dbo.UFN_ADDRESS_ADDRESSBLOCK_STANDARDUSABBREVIATIONS_LOWERCASE()

                    select @ADDRESS = replace(@ADDRESS, ' ' + [STRING1] + ' ', ' ' + [STRING2] + ' ')
                    from @ADDRESSSUBSTITUTIONS
                end

                select @ADDRESS = ltrim(rtrim(@ADDRESS))

                return @ADDRESS;
            end