UFN_CLEANCONSTITUENTSTRINGS_2

Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@FIRSTNAME	nvarchar(50)	IN
@MIDDLENAME	nvarchar(50)	IN
@KEYNAME	nvarchar(100)	IN
@ADDRESSBLOCK	nvarchar(150)	IN
@POSTCODE	nvarchar(12)	IN
Definition

Copy


create function dbo.UFN_CLEANCONSTITUENTSTRINGS_2 (
  @FIRSTNAME nvarchar(50),
  @MIDDLENAME nvarchar(50),
  @KEYNAME nvarchar(100),
  @ADDRESSBLOCK nvarchar(150),
  @POSTCODE nvarchar(12)
)
returns @CLEANSTRINGS table (
  FIRSTNAME nvarchar(50),
  MIDDLENAME nvarchar(50),
  KEYNAME nvarchar(100),
  STREETNUMBER nvarchar(12),
  STREETNAME nvarchar(150),
  POSTCODE nvarchar(12)
)
begin
  declare @CLEANADDRESSBLOCK nvarchar(150),
          @STREETNUMBER nvarchar(12),
          @STREETNAME nvarchar(150),
          @CLEANPOSTCODE nvarchar(12)

  set @CLEANADDRESSBLOCK = upper(ltrim(rtrim(replace(replace(replace(replace(replace(replace(replace(replace(
                            @ADDRESSBLOCK,'.',''),'-',' '),',',' '),
                            char(13)+char(10),' '),char(10)+char(13),' '),char(13),' '),char(10),' '),'  ',' '))))

  if dbo.UFN_REGULAREXPRESSIONMATCH('(?:^[A-Z]?[\d-]+[A-Z]? (?:.|\n)+$)',@CLEANADDRESSBLOCK) = 1 and
     charindex(' ',@CLEANADDRESSBLOCK) <= 13
  begin
    set @STREETNUMBER = left(@CLEANADDRESSBLOCK, charindex(' ',@CLEANADDRESSBLOCK)-1)
    set @STREETNAME = right(@CLEANADDRESSBLOCK, len(@CLEANADDRESSBLOCK)-charindex(' ',@CLEANADDRESSBLOCK))
  end
  else
    set @STREETNAME = @CLEANADDRESSBLOCK

  /* TODO, in order to do this, would need to clean the addresses in the searchconstituent table too
  ;with CTE as (select cast(' '+@STREETNAME+' ' as nvarchar(4000)) TRANSLATEDSTREETNAME,
                       0 LEVEL
                union all
                select replace(TRANSLATEDSTREETNAME,TOKEN,TRANSLATION),
                       LEVEL+1
                from CTE
                inner join dbo.ADDRESSTOKEN m on charindex(TOKEN,TRANSLATEDSTREETNAME) > 0 and TOKEN<>TRANSLATION
                where not exists(select 'x' from dbo.ADDRESSTOKEN m2 where m2.TOKEN<m.TOKEN and charindex(m2.TOKEN,TRANSLATEDSTREETNAME) > 0)
               )
  select top 1 @STREETNAME = ltrim(rtrim(TRANSLATEDSTREETNAME))
  from CTE
  order by LEVEL desc
  */

  set @CLEANPOSTCODE = ltrim(rtrim(@POSTCODE))

  insert into @CLEANSTRINGS
  values (isnull(upper(ltrim(rtrim(replace(@FIRSTNAME,'.','')))),''),
          isnull(upper(ltrim(rtrim(replace(@MIDDLENAME,'.','')))),''),
          isnull(upper(ltrim(rtrim(replace(replace(@KEYNAME,'.',''),'''','')))),''),
          isnull(@STREETNUMBER,''),
          isnull(@STREETNAME,''),
          case when @CLEANPOSTCODE like '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' then left(@CLEANPOSTCODE,5)
               else isnull(upper(@CLEANPOSTCODE),'') end)

  return
end