-- Contains 5000000 records
--  This table columns([PRIM_ADDRESS],[SEC_ADDRESS],	[POSTAL_CODE]) are passed to function as parameters.
CREATE TABLE [dbo].[func_temp_1](
	[ID] [numeric](18, 0) NOT NULL,
	[PRIM_ADDRESS] [varchar](80) NULL,
	[SEC_ADDRESS] [varchar](80) NULL,
	[POSTAL_CODE] [varchar](12) NULL
	PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- Contains 20 records
-- This table has some constant data used in fuction 
CREATE TABLE [dbo].[AddrName](
	[Id] [int] NOT NULL,
	[Name] [varchar](10) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


-- Function definition
CREATE FUNCTION [dbo].[func_temp] (@pi_addr1    VARCHAR (80),
                                      @pi_addr2    VARCHAR (80),
                                      @pi_zip      VARCHAR (12))
   RETURNS NVARCHAR (20)
   WITH
   SCHEMABINDING
AS
   BEGIN
      DECLARE
         @addr2                VARCHAR (80),
         @addr2_name           VARCHAR (10),
         @addr2_name_len       INT,
         @addr2_name_tab_len   INT,
         @addr2_start          INT = 0,
         @direction_abrvtn     VARCHAR (10),
         @direction_pos        INT = 0,
         @i                    INT = 0,
         @pos_1_5              VARCHAR (5) = SUBSTRING (@pi_zip, 1, 5),
         @pos_6_13             VARCHAR (80),
         @pos_14_16            VARCHAR (80),
         @pos_17               VARCHAR (1),
         @pos_18_20            VARCHAR (80),
         @streetype_abrvtn     VARCHAR (20),
         @streetype_exst       INT = 0

      DECLARE @MAX_addr2_name_id   INT
                 = (SELECT MAX (id) FROM [dbo].[AddrName])

      --Locate addr2 start position after addr2_name
      WHILE @i <= @MAX_addr2_name_id
      BEGIN
         SET @i = @i + 1
         SELECT @addr2_name = name
         FROM [dbo].[AddrName]
         WHERE id = @i

         IF CHARINDEX (@addr2_name, @pi_addr2) > 0
            BEGIN
               SET @addr2_start = CHARINDEX (@addr2_name, @pi_addr2)
               SET @addr2_name_len = LEN (@addr2_name)
               SET @i = 0
               BREAK
            END
      END

      --Remove @addr2_name from addr2
      IF @addr2_start > 0
         BEGIN
            SET @addr2 =
                   SUBSTRING (@pi_addr2, @addr2_start + @addr2_name_len, 100)
         END

      ----------------------------------------------------------------------------

      --Key positions 6-13

      --Remove non-numbers from addr1
      SET @pos_6_13 = @pi_addr1

      WHILE PATINDEX ('%[^0-9]%', @pos_6_13) > 0
         SET @pos_6_13 =
                STUFF (@pos_6_13,
                       PATINDEX ('%[^0-9]%', @pos_6_13),
                       1,
                       '')

      --If no numbers in addr1 use addr2
      IF @pos_6_13 IS NULL
         BEGIN
            SET @pos_6_13 = ISNULL (@addr2, '.')

            WHILE PATINDEX ('%[^0-9]%', @pos_6_13) > 0
               SET @pos_6_13 =
                      STUFF (@pi_addr1,
                             PATINDEX ('%[^0-9]%', @pos_6_13),
                             1,
                             '')
         END

      -- Right pad '*' up to 8th position
      SET @pos_6_13 =
             left (
                  SUBSTRING (ISNULL (@pos_6_13, '*'), 1, 8)
                + REPLICATE ('*', 8),
                8)

      ----------------------------------------------------------------------------

      --Key positions 14-16

      SET @pos_14_16 = ISNULL (@pos_14_16, @pi_addr1)

      --Get first 3 consonants from addr1 and assign to positions 14-16
      WHILE PATINDEX ('%[^BCDFGHJKLMNPQRSTVWXZ]%', @pos_14_16) > 0
         SET @pos_14_16 =
                STUFF (@pos_14_16,
                       PATINDEX ('%[^BCDFGHJKLMNPQRSTVWXZ]%', @pos_14_16),
                       1,
                       '')

      SET @pos_14_16 =
             LEFT (
                  SUBSTRING (REPLACE (@pos_14_16, ' ', ''), 1, 3)
                + REPLICATE ('*', 3),
                3)

      ----------------------------------------------------------------------------

      --Key position 17

      --assign first number from addr2 to position 17
      SET @pos_17 = SUBSTRING (REPLACE (ISNULL (@addr2, '*'), ' ', ''), 1, 1)

      ----------------------------------------------------------------------------
      --return @addr2
      --Key postions 18-20

      SET @pos_18_20 =
             RIGHT (
                  REPLICATE ('*', 3)
                + REPLACE (CAST (ISNULL (@addr2, '*') AS VARCHAR (15)),
                           ' ',
                           ''),
                3)

      ----------------------------------------------------------------------------

      RETURN @pos_1_5 + @pos_6_13 + @pos_14_16 + @pos_17 + @pos_18_20
   END