UFN_MATCHFINDER_BUILDADDRESS

            create function dbo.UFN_MATCHFINDER_BUILDADDRESS
            (
                @ADDRESSBLOCK nvarchar(150),
                @CITY nvarchar(50),
                @STATE nvarchar(100),
                @POSTCODE nvarchar(12),
                @COUNTRY nvarchar(100)
            )
            returns nvarchar(250)
            as
            begin
                declare @R nvarchar(250);
                declare @COUNTRYID uniqueidentifier;
                declare @STATEID uniqueidentifier;

                set @R = N'';
                set @COUNTRYID = null;
                set @STATEID = null;

                select top 1 @COUNTRYID = ID from dbo.COUNTRY where COUNTRY.DESCRIPTION = @COUNTRY;
                if not @COUNTRYID is null
                    select top 1 @STATEID = ID from dbo.STATE where (STATE.DESCRIPTION = @STATE or STATE.ABBREVIATION = @STATE) and STATE.COUNTRYID = @COUNTRYID;

                if not @COUNTRYID is null and not @STATEID is null
                    select @R = dbo.UFN_BUILDFULLADDRESS(null, @ADDRESSBLOCK, @CITY, @STATEID, @POSTCODE, @COUNTRYID);
                else
                begin
                    --Use a default format. This is currently based on the implementation of UFN_ADDRESS_FORMAT_US, but that function takes

                    --state and country IDs; so I re-wrote it here to use the descriptions from @STATE and @COUNTRY instead

                    if len(@ADDRESSBLOCK) > 0
                        set @R = @ADDRESSBLOCK;

                    if len(@ADDRESSBLOCK) > 0 and (len(@CITY) > 0 or len(@STATE) > 0 or len(@POSTCODE) > 0)
                        set @R = @R + NCHAR(13) + NCHAR(10);

                    if len(@CITY) > 0
                        set @R = @R + @CITY;

                    if len(@CITY) > 0 and (len(@STATE) > 0 or len(@POSTCODE) > 0)
                        set @R = @R + NCHAR(44) + NCHAR(32);    

                    if len(@STATE) > 0
                        set @R = @R + @STATE;

                    if (len(@CITY) > 0 or len(@STATE) > 0) and len(@POSTCODE) > 0
                        set @R = @R + NCHAR(32)

                    if len(@STATE) > 0 and len(@POSTCODE) > 0
                        set @R = @R + NCHAR(32)

                   if len(@POSTCODE) > 0
                        set @R = @R +@POSTCODE;
                end

                return @R;
            end