ALTER PROCEDURE [dbo].[RetUpdateStreetNameHouseNo1Company] 
	@Company			VARCHAR(100)
AS 
BEGIN
	SET @Company = REPLACE(REPLACE(@Company,'.','_'),'/','_')
	IF OBJECT_ID(N'HK Address',N'U') IS NOT NULL  
	Drop Table [HK Address] 
	
	Declare @HouseNoPlus		VARCHAR(50)
	,	@Extension 		VARCHAR(50)
	,	@IsNLPostCode		CHAR
	,	@Postcode 		VARCHAR(50)
	,	@Qry 			VARCHAR(8000) 
	,	@TableName		VARCHAR(100)
	,	@Countrycode		VARCHAR(10)  
	,	@NLCode 		VARCHAR(10)  
	,	@CharIxHouseNo 		INT
	,	@StrLenHouseNo 		INT

	DECLARE @View4Table TABLE (Id INT IDENTITY, TableName VARCHAR(100))	
	INSERT @View4Table (TableName)
		SELECT 'Contact'
	UNION	SELECT 'RetRetail Customer'
	UNION	SELECT 'Vendor' 
	UNION	SELECT 'Bank Account'  
	UNION	SELECT 'Customer' 
	DECLARE	TblCur CURSOR LOCAL FAST_FORWARD FOR 
	SELECT	TableName
	FROM	@View4Table 
	OPEN TblCur
	WHILE 1 = 1
	BEGIN
		FETCH NEXT FROM TblCur INTO @TableName
		IF @@FETCH_STATUS <> 0 BREAK
		SET @Qry = 'DROP VIEW [CompView_' + @TableName + ']'
		BEGIN TRY EXECUTE (@QRY) END TRY BEGIN CATCH END CATCH --DO NOTHING
		SET @Qry = 'CREATE VIEW [CompView_' + @TableName + '] AS SELECT * FROM [' + @Company + '$' + @TableName + ']'
		EXECUTE (@Qry)
	END
	CLOSE TblCur
	DEALLOCATE TblCur
	
	Select @NLCode = Code
	From [CompView_Country_Region]
	Where [Code] in ( 'NL', 'NLD' )
	
	IF @@ROWCOUNT > 1
	  Select @NLCode = Case When NL_Count > NLD_Count Then 'NL' Else 'NLD' END
	  From 
	  (	Select SUM(Case When [Country_Region Code] = 'NL' Then 1 Else 0 End) as [NL_Count]
		     , SUM(Case When [Country_Region Code] = 'NLD' Then 1 Else 0 End) as [NLD_Count]
		From [CompView_Contact]
	  ) x
	
	Declare @Adres TABLE
	(	[ID] integer identity(1,1) Primary Key Clustered
	  , [Table No_] integer
	  , [No_] varchar(20) COLLATE database_default
	  , [Address] varchar(50)  COLLATE database_default
	  , [Post Code] varchar(20)  COLLATE database_default
	  , [City] varchar(30)  COLLATE database_default
	  , [Country_Region Code] varchar(10)  COLLATE database_default
	  , [Street] varchar(50)  COLLATE database_default
	  , [House No_] varchar(50) COLLATE database_default
	  , [House No_ Extension] varchar(50)  COLLATE database_default
	  , UNIQUE ( [Table No_], [No_], [ID] )
	)
	
	Declare @Specials TABLE
	(	[PostCode] varchar(10) COLLATE database_default PRIMARY KEY CLUSTERED,
		[Zoekstring] varchar(10) COLLATE database_default
	)
	  
	Declare @Tags TABLE
	(  [Zoekstring] varchar(50) COLLATE  database_default PRIMARY KEY CLUSTERED
	) 
	  
	Insert @Specials
	Select UPPER([Postcode]) as [Postcode]
	     , lower([zoekstring]) as [zoekstring]
	From
	(
  Select '1063KP' COLLATE  database_default as [Postcode], '45' COLLATE database_default as [zoekstring]
  union all Select '1063KR' COLLATE  database_default, '45' COLLATE database_default
  union all Select '1064SW' COLLATE  database_default, '45' COLLATE database_default
  union all Select '1156AZ' COLLATE  database_default, 'buurt 1' COLLATE database_default
  union all Select '1156BA' COLLATE  database_default, 'buurt 1' COLLATE database_default
  union all Select '1156BB' COLLATE  database_default, 'buurt 2' COLLATE database_default
  union all Select '1156BC' COLLATE  database_default, 'buurt 2' COLLATE database_default
  union all Select '1156BD' COLLATE  database_default, 'buurt 3' COLLATE database_default
  union all Select '1156BE' COLLATE  database_default, 'buurt 3' COLLATE database_default
  union all Select '1156BG' COLLATE  database_default, 'buurt 4' COLLATE database_default
  union all Select '1156BH' COLLATE  database_default, 'buurt 4' COLLATE database_default
  union all Select '1182AW' COLLATE  database_default, '9' COLLATE database_default
  union all Select '1216BD' COLLATE  database_default, 'arm' COLLATE database_default
  union all Select '1222NG' COLLATE  database_default, '45' COLLATE database_default
  union all Select '1222NH' COLLATE  database_default, '45' COLLATE database_default
  union all Select '1222NJ' COLLATE  database_default, '45' COLLATE database_default
  union all Select '1222NK' COLLATE  database_default, '45' COLLATE database_default
  union all Select '1222NL' COLLATE  database_default, '45' COLLATE database_default
  union all Select '1251MA' COLLATE  database_default, '45' COLLATE database_default
  union all Select '1271KK' COLLATE  database_default, '2000' COLLATE database_default
  union all Select '1271KL' COLLATE  database_default, '2000' COLLATE database_default
  union all Select '1271KM' COLLATE  database_default, '2000' COLLATE database_default
  union all Select '1271KN' COLLATE  database_default, '2000' COLLATE database_default
  union all Select '1271KP' COLLATE  database_default, '2000' COLLATE database_default
  union all Select '1318EL' COLLATE  database_default, '45' COLLATE database_default
  union all Select '1398PW' COLLATE  database_default, 'nz' COLLATE  database_default
  union all Select '1398PZ' COLLATE  database_default, 'zz' COLLATE  database_default
  union all Select '1411SL' COLLATE  database_default, '45' COLLATE database_default
  union all Select '1427AT' COLLATE  database_default, 'bedijking' COLLATE database_default
  union all Select '1462MZ' COLLATE  database_default, '7' COLLATE database_default
  union all Select '1463PZ' COLLATE  database_default, '7' COLLATE database_default
  union all Select '1521AP' COLLATE  database_default, '13' COLLATE database_default
  union all Select '1755NH' COLLATE  database_default, '45' COLLATE database_default
  union all Select '1755NJ' COLLATE  database_default, '45' COLLATE database_default
  union all Select '1814XE' COLLATE  database_default, 'a9' COLLATE database_default
  union all Select '1911EW' COLLATE  database_default, '45' COLLATE database_default
  union all Select '1971GA' COLLATE  database_default, '45' COLLATE database_default
  union all Select '1971GB' COLLATE  database_default, '45' COLLATE database_default
  union all Select '1971GC' COLLATE  database_default, '45' COLLATE database_default
  union all Select '2132MA' COLLATE  database_default, '4' COLLATE database_default
  union all Select '2141BZ' COLLATE  database_default, '205' COLLATE database_default
  union all Select '2172KA' COLLATE  database_default, '44' COLLATE database_default
  union all Select '2295PK' COLLATE  database_default, '57' COLLATE database_default
  union all Select '2295PL' COLLATE  database_default, '57' COLLATE database_default
  union all Select '2322VW' COLLATE  database_default, '11' COLLATE database_default
  union all Select '2323LJ' COLLATE  database_default, '4' COLLATE database_default
  union all Select '2343ZZ' COLLATE  database_default, '44' COLLATE database_default
  union all Select '2353ZW' COLLATE  database_default, '4' COLLATE database_default
  union all Select '2411WZ' COLLATE  database_default, '11' COLLATE database_default
  union all Select '2411ZZ' COLLATE  database_default, '12' COLLATE database_default
  union all Select '2514JN' COLLATE  database_default, '1813' COLLATE database_default
  union all Select '2629HA' COLLATE  database_default, '13' COLLATE database_default
  union all Select '2631WL' COLLATE  database_default, '5e' COLLATE database_default
  union all Select '2631WP' COLLATE  database_default, '5e' COLLATE database_default
  union all Select '2631WR' COLLATE  database_default, '5e' COLLATE database_default
  union all Select '2651JZ' COLLATE  database_default, '95' COLLATE database_default
  union all Select '2716RC' COLLATE  database_default, '12' COLLATE database_default
  union all Select '2811NT' COLLATE  database_default, '12' COLLATE database_default
  union all Select '2913LW' COLLATE  database_default, '20' COLLATE database_default
  union all Select '2952BN' COLLATE  database_default, '15' COLLATE database_default
  union all Select '2988AR' COLLATE  database_default, '16' COLLATE database_default
  union all Select '2989AT' COLLATE  database_default, '15' COLLATE database_default
  union all Select '3086ED' COLLATE  database_default, '1953' COLLATE database_default
  union all Select '3086EE' COLLATE  database_default, '1953' COLLATE database_default
  union all Select '3086EG' COLLATE  database_default, '1953' COLLATE database_default
  union all Select '3086EH' COLLATE  database_default, '1953' COLLATE database_default
  union all Select '3086EJ' COLLATE  database_default, '1953' COLLATE database_default
  union all Select '3086EK' COLLATE  database_default, '1953' COLLATE database_default
  union all Select '3086EL' COLLATE  database_default, '1953' COLLATE database_default
  union all Select '3086EM' COLLATE  database_default, '1953' COLLATE database_default
  union all Select '3132TX' COLLATE  database_default, '20' COLLATE database_default
  union all Select '3135PR' COLLATE  database_default, '1940' COLLATE database_default
  union all Select '3138HB' COLLATE  database_default, '20' COLLATE database_default
  union all Select '3141RE' COLLATE  database_default, '45' COLLATE database_default
  union all Select '3161GP' COLLATE  database_default, '15' COLLATE database_default
  union all Select '3255LH' COLLATE  database_default, '59' COLLATE database_default
  union all Select '3274LS' COLLATE  database_default, '29' COLLATE database_default
  union all Select '3281LZ' COLLATE  database_default, '29' COLLATE database_default
  union all Select '3313CV' COLLATE  database_default, '45' COLLATE database_default
  union all Select '3316EK' COLLATE  database_default, '16' COLLATE database_default
  union all Select '3316ES' COLLATE  database_default, '43' COLLATE database_default
  union all Select '3342LE' COLLATE  database_default, '16' COLLATE database_default
  union all Select '3433NW' COLLATE  database_default, '27' COLLATE database_default
  union all Select '3437PV' COLLATE  database_default, '2' COLLATE database_default
  union all Select '3454CN' COLLATE  database_default, '1954' COLLATE database_default
  union all Select '3454CP' COLLATE  database_default, '1954' COLLATE database_default
  union all Select '3454CR' COLLATE  database_default, '1954' COLLATE database_default
  union all Select '3481CV' COLLATE  database_default, '12' COLLATE database_default
  union all Select '3641PH' COLLATE  database_default, 'bedijking' COLLATE database_default
  union all Select '3642EZ' COLLATE  database_default, 'bedijking' COLLATE database_default
  union all Select '3732XN' COLLATE  database_default, '1813' COLLATE database_default
  union all Select '3755MN' COLLATE  database_default, '27' COLLATE database_default
  union all Select '3818EX' COLLATE  database_default, '1914' COLLATE database_default
  union all Select '3818EZ' COLLATE  database_default, '1914' COLLATE database_default
  union all Select '3821CH' COLLATE  database_default, '1' COLLATE database_default
  union all Select '3828NZ' COLLATE  database_default, '1' COLLATE database_default
  union all Select '3829MZ' COLLATE  database_default, '28' COLLATE database_default
  union all Select '3841JA' COLLATE  database_default, '45' COLLATE database_default
  union all Select '3841JB' COLLATE  database_default, '45' COLLATE database_default
  union all Select '3841JC' COLLATE  database_default, '45' COLLATE database_default
  union all Select '3841JD' COLLATE  database_default, '45' COLLATE database_default
  union all Select '3845LZ' COLLATE  database_default, '28' COLLATE database_default
  union all Select '3882SR' COLLATE  database_default, '28' COLLATE database_default
  union all Select '3925JM' COLLATE  database_default, '40' COLLATE database_default
  union all Select '3931CR' COLLATE  database_default, '45' COLLATE database_default
  union all Select '3931CS' COLLATE  database_default, '45' COLLATE database_default
  union all Select '3941ER' COLLATE  database_default, '1923' COLLATE database_default
  union all Select '3941ES' COLLATE  database_default, '1923' COLLATE database_default
  union all Select '3981HK' COLLATE  database_default, '12' COLLATE database_default
  union all Select '4043HA' COLLATE  database_default, '15' COLLATE database_default
  union all Select '4051CZ' COLLATE  database_default, '15' COLLATE database_default
  union all Select '4141DD' COLLATE  database_default, '2' COLLATE  database_default
  union all Select '4153RL' COLLATE  database_default, '2' COLLATE database_default
  union all Select '4156JJ' COLLATE  database_default, '15' COLLATE database_default
  union all Select '4191NL' COLLATE  database_default, '15' COLLATE database_default
  union all Select '4194RG' COLLATE  database_default, '45' COLLATE database_default
  union all Select '4194RH' COLLATE  database_default, '45' COLLATE database_default
  union all Select '4194VG' COLLATE  database_default, '45' COLLATE database_default
  union all Select '4194VH' COLLATE  database_default, '45' COLLATE database_default
  union all Select '4194WN' COLLATE  database_default, '45' COLLATE database_default
  union all Select '4194WP' COLLATE  database_default, '45' COLLATE database_default
  union all Select '4194WR' COLLATE  database_default, '45' COLLATE database_default
  union all Select '4194WS' COLLATE  database_default, '45' COLLATE database_default
  union all Select '4221MZ' COLLATE  database_default, '27' COLLATE database_default
  union all Select '4231EE' COLLATE  database_default, '27' COLLATE database_default
  union all Select '4273ZB' COLLATE  database_default, '27' COLLATE database_default
  union all Select '4331LG' COLLATE  database_default, '1940' COLLATE database_default
  union all Select '4331LH' COLLATE  database_default, '1940' COLLATE database_default
  union all Select '4336CA' COLLATE  database_default, 've 1' COLLATE database_default
  union all Select '4336CB' COLLATE  database_default, 've 2' COLLATE database_default
  union all Select '4337VA' COLLATE  database_default, '1' COLLATE database_default
  union all Select '4337VB' COLLATE  database_default, '2' COLLATE database_default
  union all Select '4587LJ' COLLATE  database_default, '60' COLLATE database_default
  union all Select '4611AZ' COLLATE  database_default, '3' COLLATE  database_default
  union all Select '4623RM' COLLATE  database_default, '58' COLLATE database_default
  union all Select '4652AR' COLLATE  database_default, '45' COLLATE database_default
  union all Select '4681AS' COLLATE  database_default, '53' COLLATE database_default
  union all Select '4765CK' COLLATE  database_default, '1940' COLLATE database_default
  union all Select '4765SH' COLLATE  database_default, '16' COLLATE database_default
  union all Select '4765TK' COLLATE  database_default, '16' COLLATE database_default
  union all Select '4791SZ' COLLATE  database_default, '17' COLLATE database_default
  union all Select '4793RZ' COLLATE  database_default, '59' COLLATE database_default
  union all Select '4849RK' COLLATE  database_default, '27' COLLATE database_default
  union all Select '4851RD' COLLATE  database_default, '3a' COLLATE database_default
  union all Select '4872LB' COLLATE  database_default, '58' COLLATE database_default
  union all Select '4903RP' COLLATE  database_default, '27' COLLATE database_default
  union all Select '4911BV' COLLATE  database_default, '59' COLLATE database_default
  union all Select '5046KC' COLLATE  database_default, '13' COLLATE database_default
  union all Select '5051LS' COLLATE  database_default, '1803' COLLATE database_default
  union all Select '5066PZ' COLLATE  database_default, '58' COLLATE database_default
  union all Select '5081VA' COLLATE  database_default, '45' COLLATE database_default
  union all Select '5126RX' COLLATE  database_default, '58' COLLATE database_default
  union all Select '5334LH' COLLATE  database_default, 'geerden' COLLATE database_default
  union all Select '5341VA' COLLATE  database_default, '45' COLLATE database_default
  union all Select '5341VB' COLLATE  database_default, '45' COLLATE database_default
  union all Select '5341VC' COLLATE  database_default, '45' COLLATE database_default
  union all Select '5348PH' COLLATE  database_default, '45' COLLATE database_default
  union all Select '5348PJ' COLLATE  database_default, '45' COLLATE database_default
  union all Select '5348PK' COLLATE  database_default, '45' COLLATE database_default
  union all Select '5348PL' COLLATE  database_default, '45' COLLATE database_default
  union all Select '5348PM' COLLATE  database_default, '45' COLLATE database_default
  union all Select '5348PN' COLLATE  database_default, '45' COLLATE database_default
  union all Select '5348PP' COLLATE  database_default, '45' COLLATE database_default
  union all Select '5348PR' COLLATE  database_default, '45' COLLATE database_default
  union all Select '5348PS' COLLATE  database_default, '45' COLLATE database_default
  union all Select '5348PT' COLLATE  database_default, '45' COLLATE database_default
  union all Select '5348PV' COLLATE  database_default, '45' COLLATE database_default
  union all Select '5348VR' COLLATE  database_default, '45' COLLATE database_default
  union all Select '5375KX' COLLATE  database_default, '321' COLLATE database_default
  union all Select '5375KZ' COLLATE  database_default, '321' COLLATE database_default
  union all Select '5473CA' COLLATE  database_default, '1969' COLLATE database_default
  union all Select '5473CW' COLLATE  database_default, '1969' COLLATE database_default
  union all Select '5481AP' COLLATE  database_default, '1944' COLLATE database_default
  union all Select '5481AR' COLLATE  database_default, '1944' COLLATE database_default
  union all Select '5504RS' COLLATE  database_default, '34' COLLATE database_default
  union all Select '5661PG' COLLATE  database_default, '3' COLLATE database_default
  union all Select '5664TH' COLLATE  database_default, 'kinderen' COLLATE database_default
  union all Select '5664TJ' COLLATE  database_default, 'kinderen' COLLATE database_default
  union all Select '5688PZ' COLLATE  database_default, '58' COLLATE database_default
  union all Select '5691PL' COLLATE  database_default, '50' COLLATE database_default
  union all Select '5721SG' COLLATE  database_default, 'a67' COLLATE database_default
  union all Select '5735GK' COLLATE  database_default, 'gehuchten' COLLATE database_default
  union all Select '5825AN' COLLATE  database_default, '1944' COLLATE database_default
  union all Select '5993BR' COLLATE  database_default, '1786' COLLATE database_default
  union all Select '5993RM' COLLATE  database_default, '67' COLLATE database_default
  union all Select '6004RW' COLLATE  database_default, '2' COLLATE database_default
  union all Select '6011PE' COLLATE  database_default, '2' COLLATE database_default
  union all Select '6101XG' COLLATE  database_default, 'a2' COLLATE database_default
  union all Select '6116AE' COLLATE  database_default, '2' COLLATE  database_default
  union all Select '6137SL' COLLATE  database_default, 'sittard' COLLATE database_default
  union all Select '6137SM' COLLATE  database_default, 'sittard' COLLATE database_default
  union all Select '6137SN' COLLATE  database_default, 'sittard' COLLATE database_default
  union all Select '6137SP' COLLATE  database_default, 'sittard' COLLATE database_default
  union all Select '6167RE' COLLATE  database_default, 'a2' COLLATE database_default
  union all Select '6191RS' COLLATE  database_default, 'a2' COLLATE database_default
  union all Select '6221JP' COLLATE  database_default, '1992' COLLATE database_default
  union all Select '6422PW' COLLATE  database_default, '76' COLLATE database_default
  union all Select '6511JB' COLLATE  database_default, '1944' COLLATE database_default
  union all Select '6511JC' COLLATE  database_default, '1944' COLLATE database_default
  union all Select '6511JD' COLLATE  database_default, '1944' COLLATE database_default
  union all Select '6511JE' COLLATE  database_default, '1944' COLLATE database_default
  union all Select '6511JG' COLLATE  database_default, '1944' COLLATE database_default
  union all Select '6511JH' COLLATE  database_default, '1944' COLLATE database_default
  union all Select '6551CS' COLLATE  database_default, '1945' COLLATE database_default
  union all Select '6551CT' COLLATE  database_default, '1945' COLLATE database_default
  union all Select '6551CV' COLLATE  database_default, '1945' COLLATE database_default
  union all Select '6551CW' COLLATE  database_default, '1945' COLLATE database_default
  union all Select '6551CX' COLLATE  database_default, '1945' COLLATE database_default
  union all Select '6551CZ' COLLATE  database_default, '1945' COLLATE database_default
  union all Select '6562DL' COLLATE  database_default, 'jes' COLLATE  database_default
  union all Select '6562DM' COLLATE  database_default, 'jes' COLLATE  database_default
  union all Select '6661ZW' COLLATE  database_default, 'ren' COLLATE  database_default
  union all Select '6661ZX' COLLATE  database_default, 'ren' COLLATE  database_default
  union all Select '6661ZZ' COLLATE  database_default, 'ren' COLLATE  database_default
  union all Select '6662MZ' COLLATE  database_default, '325' COLLATE database_default
  union all Select '6663CB' COLLATE  database_default, 'a52' COLLATE database_default
  union all Select '6676LE' COLLATE  database_default, '50' COLLATE database_default
  union all Select '6708AL' COLLATE  database_default, 'augustus' COLLATE database_default
  union all Select '6708AM' COLLATE  database_default, 'augustus' COLLATE database_default
  union all Select '6708TH' COLLATE  database_default, 'uilen' COLLATE database_default
  union all Select '6711NX' COLLATE  database_default, '1933' COLLATE database_default
  union all Select '6711NZ' COLLATE  database_default, '1933' COLLATE database_default
  union all Select '6711PA' COLLATE  database_default, '1948' COLLATE database_default
  union all Select '6718VZ' COLLATE  database_default, '30' COLLATE database_default
  union all Select '6816VZ' COLLATE  database_default, '50' COLLATE database_default
  union all Select '6824HX' COLLATE  database_default, '1813' COLLATE database_default
  union all Select '6842AM' COLLATE  database_default, 'kamp' COLLATE  database_default
  union all Select '6846MB' COLLATE  database_default, 'mei' COLLATE database_default
  union all Select '6846MK' COLLATE  database_default, 'september' COLLATE database_default
  union all Select '6846ML' COLLATE  database_default, 'september' COLLATE database_default
  union all Select '6862DV' COLLATE  database_default, '1946' COLLATE database_default
  union all Select '6909DL' COLLATE  database_default, 'a12' COLLATE database_default
  union all Select '6909DR' COLLATE  database_default, 'a12' COLLATE database_default
  union all Select '6921AB' COLLATE  database_default, '12' COLLATE database_default
  union all Select '6942PW' COLLATE  database_default, '18' COLLATE database_default
  union all Select '7002DC' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7002DD' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7226LJ' COLLATE  database_default, '1344' COLLATE database_default
  union all Select '7231GJ' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7231GK' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7231GL' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7231GM' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7339EL' COLLATE  database_default, 'a1' COLLATE database_default
  union all Select '7339ET' COLLATE  database_default, 'a1' COLLATE database_default
  union all Select '7348BK' COLLATE  database_default, 'a1' COLLATE database_default
  union all Select '7381AZ' COLLATE  database_default, 'a50' COLLATE database_default
  union all Select '7384DZ' COLLATE  database_default, 'a1' COLLATE database_default
  union all Select '7451NX' COLLATE  database_default, 'a1' COLLATE database_default
  union all Select '7461MC' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7461MD' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7511AA' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7511AB' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7511AC' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7511AD' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7511AE' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7511AG' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7511AH' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7511AJ' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7511AK' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7511AL' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7511AM' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7511NG' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7511NH' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7548TE' COLLATE  database_default, 'a35' COLLATE database_default
  union all Select '7553BJ' COLLATE  database_default, '1918' COLLATE database_default
  union all Select '7561VA' COLLATE  database_default, 'a1' COLLATE database_default
  union all Select '7623AK' COLLATE  database_default, 'horst' COLLATE  database_default
  union all Select '7623AM' COLLATE  database_default, 'horst' COLLATE  database_default
  union all Select '7623AS' COLLATE  database_default, 'horst' COLLATE  database_default
  union all Select '7671MK' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7671ML' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7671MN' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7671MP' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7671MR' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7671MS' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7671MT' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7671MV' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7671MX' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7671MZ' COLLATE  database_default, '45' COLLATE database_default
  union all Select '7671SR' COLLATE  database_default, '36' COLLATE database_default
  union all Select '7701RC' COLLATE  database_default, 'wijk' COLLATE database_default
  union all Select '7701RE' COLLATE  database_default, 'wijk' COLLATE database_default
  union all Select '7701RR' COLLATE  database_default, 'wijk' COLLATE database_default
  union all Select '7741NG' COLLATE  database_default, 'n34' COLLATE database_default
  union all Select '7814TX' COLLATE  database_default, 'n34' COLLATE database_default
  union all Select '7876GG' COLLATE  database_default, '1917' COLLATE database_default
  union all Select '7924PV' COLLATE  database_default, 'a28' COLLATE database_default
  union all Select '7973KT' COLLATE  database_default, '32' COLLATE database_default
  union all Select '8091ZZ' COLLATE  database_default, 'a28' COLLATE database_default
  union all Select '8181MH' COLLATE  database_default, 'a50' COLLATE database_default
  union all Select '8218PW' COLLATE  database_default, 'ver' COLLATE  database_default
  union all Select '8218PX' COLLATE  database_default, 'lepelaar' COLLATE database_default
  union all Select '8276BZ' COLLATE  database_default, 'n50' COLLATE database_default
  union all Select '8308TA' COLLATE  database_default, '6' COLLATE database_default
  union all Select '8313RP' COLLATE  database_default, 'a6' COLLATE database_default
  union all Select '8314RZ' COLLATE  database_default, 'a6' COLLATE database_default
  union all Select '8321EK' COLLATE  database_default, 'wijk 1' COLLATE database_default
  union all Select '8321EL' COLLATE  database_default, 'wijk 1' COLLATE database_default
  union all Select '8321EM' COLLATE  database_default, 'wijk 1' COLLATE database_default
  union all Select '8321EN' COLLATE  database_default, 'wijk 1' COLLATE database_default
  union all Select '8321EP' COLLATE  database_default, 'wijk 2' COLLATE database_default
  union all Select '8321ER' COLLATE  database_default, 'wijk 2' COLLATE database_default
  union all Select '8321ES' COLLATE  database_default, 'wijk 2' COLLATE database_default
  union all Select '8321ET' COLLATE  database_default, 'wijk 2' COLLATE database_default
  union all Select '8321EV' COLLATE  database_default, 'wijk 2' COLLATE database_default
  union all Select '8321EW' COLLATE  database_default, 'wijk 3' COLLATE database_default
  union all Select '8321EX' COLLATE  database_default, 'wijk 3' COLLATE database_default
  union all Select '8321EZ' COLLATE  database_default, 'wijk 3' COLLATE database_default
  union all Select '8321GA' COLLATE  database_default, 'wijk 3' COLLATE database_default
  union all Select '8321GB' COLLATE  database_default, 'wijk 3' COLLATE database_default
  union all Select '8321GC' COLLATE  database_default, 'wijk 4' COLLATE database_default
  union all Select '8321GD' COLLATE  database_default, 'wijk 4' COLLATE database_default
  union all Select '8321GE' COLLATE  database_default, 'wijk 4' COLLATE database_default
  union all Select '8321GG' COLLATE  database_default, 'wijk 4' COLLATE database_default
  union all Select '8321GH' COLLATE  database_default, 'wijk 4' COLLATE database_default
  union all Select '8321GJ' COLLATE  database_default, 'wijk 4' COLLATE database_default
  union all Select '8321GK' COLLATE  database_default, 'wijk 5' COLLATE database_default
  union all Select '8321GL' COLLATE  database_default, 'wijk 5' COLLATE database_default
  union all Select '8321GM' COLLATE  database_default, 'wijk 5' COLLATE database_default
  union all Select '8321GN' COLLATE  database_default, 'wijk 5' COLLATE database_default
  union all Select '8321GP' COLLATE  database_default, 'wijk 5' COLLATE database_default
  union all Select '8321GR' COLLATE  database_default, 'wijk 5' COLLATE database_default
  union all Select '8321GS' COLLATE  database_default, 'wijk 5' COLLATE database_default
  union all Select '8321TA' COLLATE  database_default, 'wijk 7' COLLATE database_default
  union all Select '8321TB' COLLATE  database_default, 'wijk 7' COLLATE database_default
  union all Select '8321TC' COLLATE  database_default, 'wijk 7' COLLATE database_default
  union all Select '8321TD' COLLATE  database_default, 'wijk 7' COLLATE database_default
  union all Select '8321TE' COLLATE  database_default, 'wijk 7' COLLATE database_default
  union all Select '8321TG' COLLATE  database_default, 'wijk 8' COLLATE database_default
  union all Select '8321TH' COLLATE  database_default, 'wijk 8' COLLATE database_default
  union all Select '8321TJ' COLLATE  database_default, 'wijk 8' COLLATE database_default
  union all Select '8321TK' COLLATE  database_default, 'wijk 8' COLLATE database_default
  union all Select '8321TL' COLLATE  database_default, 'wijk 8' COLLATE database_default
  union all Select '8321VA' COLLATE  database_default, 'wijk 6' COLLATE database_default
  union all Select '8321VB' COLLATE  database_default, 'wijk 6' COLLATE database_default
  union all Select '8321VC' COLLATE  database_default, 'wijk 6' COLLATE database_default
  union all Select '8321VD' COLLATE  database_default, 'wijk 6' COLLATE database_default
  union all Select '8321VE' COLLATE  database_default, 'wijk 6' COLLATE database_default
  union all Select '8321VG' COLLATE  database_default, 'wijk 6' COLLATE database_default
  union all Select '8321VH' COLLATE  database_default, 'wijk 6' COLLATE database_default
  union all Select '8321VJ' COLLATE  database_default, 'wijk 6' COLLATE database_default
  union all Select '8321VK' COLLATE  database_default, 'wijk 7' COLLATE database_default
  union all Select '8321VL' COLLATE  database_default, 'wijk 7' COLLATE database_default
  union all Select '8321VM' COLLATE  database_default, 'wijk 7' COLLATE database_default
  union all Select '8407GZ' COLLATE  database_default, 'a7' COLLATE database_default
  union all Select '8411TK' COLLATE  database_default, 'wijk' COLLATE  database_default
  union all Select '8411TL' COLLATE  database_default, 'wijk' COLLATE  database_default
  union all Select '8412SB' COLLATE  database_default, 'Wyk' COLLATE  database_default
  union all Select '8412SE' COLLATE  database_default, 'Wyk' COLLATE  database_default
  union all Select '8412SG' COLLATE  database_default, 'Wyk' COLLATE  database_default
  union all Select '8412SJ' COLLATE  database_default, 'Wyk' COLLATE  database_default
  union all Select '8412SK' COLLATE  database_default, 'Wyk' COLLATE  database_default
  union all Select '8412SL' COLLATE  database_default, 'Wyk' COLLATE  database_default
  union all Select '8412TB' COLLATE  database_default, 'Wyk' COLLATE  database_default
  union all Select '8412TD' COLLATE  database_default, 'Wyk' COLLATE  database_default
  union all Select '8412TE' COLLATE  database_default, 'Wyk' COLLATE  database_default
  union all Select '8412TH' COLLATE  database_default, 'Wyk' COLLATE  database_default
  union all Select '8412TK' COLLATE  database_default, 'Wyk' COLLATE  database_default
  union all Select '8413NM' COLLATE  database_default, 'wijk' COLLATE  database_default
  union all Select '8414NA' COLLATE  database_default, 'wijk' COLLATE  database_default
  union all Select '8433LA' COLLATE  database_default, 'boven' COLLATE database_default
  union all Select '8433LB' COLLATE  database_default, 'boven' COLLATE database_default
  union all Select '8433LC' COLLATE  database_default, 'boven' COLLATE database_default
  union all Select '8433LD' COLLATE  database_default, 'boven' COLLATE database_default
  union all Select '8433LE' COLLATE  database_default, 'boven' COLLATE database_default
  union all Select '8477BZ' COLLATE  database_default, 'a32' COLLATE database_default
  union all Select '8526GJ' COLLATE  database_default, 'a7' COLLATE database_default
  union all Select '8601GT' COLLATE  database_default, 'buurt' COLLATE  database_default
  union all Select '8701EW' COLLATE  database_default, '1455' COLLATE  database_default
  union all Select '8747NK' COLLATE  database_default, 'a7' COLLATE  database_default
  union all Select '8749TT' COLLATE  database_default, 'e10' COLLATE  database_default
  union all Select '8751ZZ' COLLATE  database_default, 'n31' COLLATE  database_default
  union all Select '9077RC' COLLATE  database_default, 'mom' COLLATE  database_default
  union all Select '9218VH' COLLATE  database_default, 'z' COLLATE  database_default
  union all Select '9331WP' COLLATE  database_default, 'nen 1' COLLATE  database_default
  union all Select '9331WR' COLLATE  database_default, 'nen 2' COLLATE  database_default
  union all Select '9331WS' COLLATE  database_default, 'nen 3' COLLATE  database_default
  union all Select '9331WT' COLLATE  database_default, 'nen 4' COLLATE  database_default
  union all Select '9331WV' COLLATE  database_default, 'nen 5' COLLATE  database_default
  union all Select '9364TZ' COLLATE  database_default, 'a7' COLLATE  database_default
  union all Select '9365TK' COLLATE  database_default, 'a7' COLLATE  database_default
  union all Select '9411TW' COLLATE  database_default, 'a28' COLLATE  database_default
  union all Select '9541XA' COLLATE  database_default, 'west 1' COLLATE  database_default
  union all Select '9541XK' COLLATE  database_default, 'west 2' COLLATE  database_default
  union all Select '9541XX' COLLATE  database_default, 'oost 3' COLLATE  database_default
  union all Select '9551XE' COLLATE  database_default, 'west 4' COLLATE  database_default
  union all Select '9551XH' COLLATE  database_default, 'oost 4' COLLATE  database_default
  union all Select '9561TJ' COLLATE  database_default, 'oost 5' COLLATE  database_default
  union all Select '9561TP' COLLATE  database_default, 'west 5' COLLATE  database_default
  union all Select '9566TL' COLLATE  database_default, 'west 1' COLLATE  database_default
  union all Select '9616TN' COLLATE  database_default, 'a7' COLLATE  database_default
  union all Select '9636TL' COLLATE  database_default, 'a7' COLLATE  database_default
  union all Select '9648TA' COLLATE  database_default, 'weg 12' COLLATE  database_default
  union all Select '9679CV' COLLATE  database_default, '1509' COLLATE  database_default
  union all Select '9679CX' COLLATE  database_default, '1509' COLLATE  database_default
  union all Select '9679CZ' COLLATE  database_default, '1509' COLLATE  database_default
  union all Select '9679TN' COLLATE  database_default, 'a7' COLLATE  database_default
  union all Select '9693CK' COLLATE  database_default, '42' COLLATE  database_default
  union all Select '9743AZ' COLLATE  database_default, '45' COLLATE  database_default
  union all Select '9752XZ' COLLATE  database_default, 'a28' COLLATE  database_default
	) sp
	
	INSERT @Specials
	Select LEFT([Postcode],4) + ' ' + Right([Postcode],2) as [Postcode]
	     , [Zoekstring]
	From @Specials
	
	Insert @Tags
	Select lower([Zoekstring]) as [Zoekstring]
	From
	(
	  Select convert(varchar(50),'zuid') COLLATE  database_default as [Zoekstring]
	  union Select 'noord'
	  union Select 'oost'
	  union Select 'west'
	  union Select 'str' 
	  union Select 'str.'
	  union Select 'straat'
	  union Select 'weg'
	  union Select 'laan'
	  union Select 'dijk'
	  union Select 'gracht'
	  union Select 'plein'
	  union Select 'baan'
	  union Select 'state'
	  union Select 'hof'
	  union Select 'kade'
	  union Select 'dreef'
	  union Select 'singel'
	  union Select 'markt'
	  union Select 'steeg'
	  union Select 'pad'
	  union Select 'wal'
	  union Select 'lei'
	  union Select 'vaart'
	  union Select 'berg'
	  union Select 'bos'
	  union Select 'erf'
	  union Select 'centrum'
	  union Select 'beneden'
	  union Select 'ln.'
	  union Select 'ln '
	  union Select 'postbus'
  union Select 'laantje'
  union Select 'ring'
  union Select 'wijkje'
  union Select 'mond'
  union Select 'haven'
  union Select 'zijde'
  union Select 'veld'
  union Select 'poort'
  union Select 'vijfde'
	) t  
	
	INSERT @Adres
	([Table No_],[No_],[Address],[Post code],[City],[Country_Region Code]) 
	Select 5050 as [Table No_]
	     , [No_] COLLATE  database_default as [No_]
     , REPLACE(REPLACE(LTRIM(RTRIM([Address])),'  ',' '),'  ',' ') COLLATE  database_default as [Address]
	     , UPPER([Post Code]) COLLATE  database_default AS [Post Code]
	     , [City] COLLATE  database_default AS [City]
	     , [Country_Region Code] COLLATE  database_default AS [Country_Region Code]
	FROM [CompView_Contact]
	WHERE [Address] <> ''
	
	INSERT INTO @Adres ([Table No_],[No_],[Address],[Post code],[City],[Country_Region Code]) 
	SELECT 11019278 
	     , [No_] COLLATE  database_default
     , REPLACE(REPLACE(LTRIM(RTRIM([Address])),'  ',' '),'  ',' ') COLLATE  database_default as [Address]
	     , UPPER([Post Code]) COLLATE  database_default as [Post Code]
	     , [City] COLLATE  database_default 
	     , [Country_Region Code] COLLATE  database_default 
	From [CompView_RetRetail Customer]
	Where [Address] <> ''
	
	INSERT INTO @Adres ([Table No_],[No_],[Address],[Post code],[City],[Country_Region Code]) 
	SELECT 23 
	    , [No_] COLLATE  database_default  
    , REPLACE(REPLACE(LTRIM(RTRIM([Address])),'  ',' '),'  ',' ') COLLATE  database_default 
	    , UPPER([Post Code]) COLLATE  database_default as [Post Code]
	    , [City] COLLATE  database_default 
	    , [Country_Region Code] COLLATE  database_default 
	From [CompView_Vendor] 
	Where [Address] <> ''  
	 
	INSERT INTO @Adres ([Table No_],[No_],[Address],[Post code],[City],[Country_Region Code]) 
	SELECT 270 
	    , [No_] COLLATE  database_default  
    , REPLACE(REPLACE(LTRIM(RTRIM([Address])),'  ',' '),'  ',' ') COLLATE  database_default 
	    , UPPER([Post Code]) COLLATE  database_default as [Post Code]
	    , [City] COLLATE  database_default 
	    , [Country_Region Code] COLLATE  database_default 
	From [CompView_Bank Account] 
	Where [Address] <> ''  
	  
	INSERT INTO @Adres ([Table No_],[No_],[Address],[Post code],[City],[Country_Region Code]) 
	SELECT 18 
	    ,[No_] COLLATE  database_default  
    , REPLACE(REPLACE(LTRIM(RTRIM([Address])),'  ',' '),'  ',' ') COLLATE  database_default 
	    , UPPER([Post Code]) COLLATE  database_default as [Post Code]
	    , [City] COLLATE  database_default 
	    , [Country_Region Code] COLLATE  database_default 
	From [CompView_Customer] 
	Where [Address] <> ''  
	
	Update a
	Set @postcode = ltrim(rtrim(Replace([Post Code],' ','')))
	  , @IsNLPostcode = Case
	                      When len(@postcode) <> 6 Then 'N'
						  When isnumeric(Left(@postcode,4)) <> 1 Then 'N'
	                      When lower(left(right(@postcode,2),1)) not between 'a' and 'z' Then 'N'
	                      When lower(right(@postcode,1)) not between 'a' and 'z' Then 'N'
	                      Else 'J'
	                    End
	 , @CountryCode   = Case 
	                      When isnull([Bron],'') = 'Special' Then @NLCode
	                      When @IsNLPostcode = 'J' Then @NLCode 
	                      Else [Country_Region Code] 
	                    End  
	 , @CharIxHouseNo = Case 
						     -- geen cijfers in adres dan kan huisnr niet worden gevuld
							 When [Laatste cijfer] is null Then 0
							 -- Huisnummer begint bij het eerste cijfer na de tag
							 When [Bron] <> '' Then [Eerste cijfer]
							 -- Geen tag of special en buitenlands adres en adres begint met nummer dan zal dat wel het nummer zijn 
							 When [Eerste cijfer] = 1 and @CountryCode not in ( 'NL', 'NLD', 'DE', 'DEU' ) Then [Eerste cijfer]
							 -- Een enkel nummer, dan zal dat het huisnummer zijn
							 When isnumeric(substring([Address],[Eerste cijfer],[Laatste Cijfer]-[Eerste cijfer] + 1)) = 1
							  and charindex('.',substring([Address],[Eerste cijfer],[Laatste Cijfer]-[Eerste cijfer] + 1)) = 0
							  and charindex(',',substring([Address],[Eerste cijfer],[Laatste Cijfer]-[Eerste cijfer] + 1)) = 0
	                         Then [Eerste cijfer]
							 -- meerdere nummers, indien gescheiden door een enkele '-' dan de combinatie (bijv. 10-12) beschouwen als het huisnummer
							 When [Eerste cijfer] >= 4
							  and isnumeric(replace(substring([Address],[Eerste cijfer],[Laatste Cijfer]-[Eerste cijfer] + 1),'-','')) = 1
							  and len(substring([Address],[Eerste cijfer],[Laatste Cijfer]-[Eerste cijfer] + 1)) - len(replace(substring([Address],[Eerste cijfer],[Laatste Cijfer]-[Eerste cijfer] + 1),'-','')) = 1 
							 Then [Eerste cijfer]
							 -- meerdere nummers, indien gescheiden door een enkele '/' dan de combinatie (bijv. 10/12) beschouwen als het huisnummer
							 When [Eerste cijfer] >= 4
							  and isnumeric(replace(substring([Address],[Eerste cijfer],[Laatste Cijfer]-[Eerste cijfer] + 1),'/','')) = 1
							  and len(substring([Address],[Eerste cijfer],[Laatste Cijfer]-[Eerste cijfer] + 1)) - len(replace(substring([Address],[Eerste cijfer],[Laatste Cijfer]-[Eerste cijfer] + 1),'/','')) = 1 
							 Then [Eerste cijfer]
							 -- bij buitenlandse adressen met meerdere nummers kunnen we het niet bepalen
							 When @CountryCode not in ( 'NL', 'NLD', 'DE', 'DEU' )
							 Then 0
							 -- bij nederlandse adressen nemen we het laatste nummer (max 6 lang)
							 -- laatste nummer is 7 lang of meer dan is dat geen huisnummer
							 When [Laatste cijfer] >= 7 and charindex(substring([Address],[Laatste Cijfer]-6,1),'0123456789') > 0
							   and isnumeric(substring([Address],[Laatste Cijfer]-6,7)) = 1 
	                         Then 0
							 -- laatste nummer is 6 lang dan is dat het huisnummer
							 When [Laatste cijfer] >= 6 and charindex(substring([Address],[Laatste Cijfer]-5,1),'0123456789') > 0
							   and isnumeric(substring([Address],[Laatste Cijfer]-5,6)) = 1 
	   						   and [Laatste Cijfer]-5 >= 4
	                         Then [Laatste Cijfer]-5
							 -- laatste nummer is 5 lang dan is dat het huisnummer
							 When [Laatste cijfer] >= 5 and charindex(substring([Address],[Laatste Cijfer]-4,1),'0123456789') > 0
							   and isnumeric(substring([Address],[Laatste Cijfer]-4,5)) = 1 
							   and [Laatste Cijfer]-4 >= 4
	                         Then [Laatste Cijfer]-4
							 When [Laatste cijfer] >= 4 and charindex(substring([Address],[Laatste Cijfer]-3,1),'0123456789') > 0
							   and isnumeric(substring([Address],[Laatste Cijfer]-3,4)) = 1 
							   and [Laatste Cijfer]-3 >= 4
	                         Then [Laatste Cijfer]-3
							 When [Laatste cijfer] >= 3 and charindex(substring([Address],[Laatste Cijfer]-2,1),'0123456789') > 0
							   and isnumeric(substring([Address],[Laatste Cijfer]-2,3)) = 1 
							   and [Laatste Cijfer]-2 >= 4
	                         Then [Laatste Cijfer]-2
							 When [Laatste cijfer] >= 2 and charindex(substring([Address],[Laatste Cijfer]-1,1),'0123456789') > 0
							   and isnumeric(substring([Address],[Laatste Cijfer]-1,2)) = 1 
							   and [Laatste Cijfer]-1 >= 4
	                         Then [Laatste Cijfer]-1
							 When [Laatste cijfer] >= 1 and charindex(substring([Address],[Laatste Cijfer],1),'0123456789') > 0
							   and isnumeric(substring([Address],[Laatste Cijfer],1)) = 1 
							   and [Laatste Cijfer] >= 4
	                         Then [Laatste Cijfer]
	                         Else 0
						   End
	  , @CharIxHouseNo = Case
	                       When @CountryCode in ( 'NL', 'NLD', 'DE', 'DEU' ) and @CharIxHouseNo < 4 Then 0
						   Else @CharIxHouseNo
						 End
	  , @StrLenHouseNo = Case 
	                        When isnumeric(substring([Address],@CharIxHouseNo,999)) = 1 Then len(rtrim(substring([Address],@CharIxHouseNo,999)))
	                        When isnumeric(substring([Address],@CharIxHouseNo,6)) = 1 Then len(rtrim(substring([Address],@CharIxHouseNo,6)))
	                        When isnumeric(substring([Address],@CharIxHouseNo,5)) = 1 Then len(rtrim(substring([Address],@CharIxHouseNo,5)))
	                        When isnumeric(substring([Address],@CharIxHouseNo,4)) = 1 Then len(rtrim(substring([Address],@CharIxHouseNo,4)))
	                        When isnumeric(substring([Address],@CharIxHouseNo,3)) = 1 Then len(rtrim(substring([Address],@CharIxHouseNo,3)))
	                        When isnumeric(substring([Address],@CharIxHouseNo,2)) = 1 Then len(rtrim(substring([Address],@CharIxHouseNo,2)))
	                        When isnumeric(substring([Address],@CharIxHouseNo,1)) = 1 Then len(rtrim(substring([Address],@CharIxHouseNo,1)))
	                        Else 0
						  End
	  , @StrLenHouseNo = Case 
	                       When @StrLenHouseNo = 0 Then 0
						   When substring([Address],@CharIxHouseNo+@StrLenHouseNo,1) not in ( '-', '/' ) Then @StrLenHouseNo
						   When isnumeric(substring([Address],@CharIxHouseNo+@StrLenHouseNo+1,999)) = 1 Then @StrLenHouseNo + 1 + len(rtrim(substring([Address],@CharIxHouseNo+@StrLenHouseNo+1,999)))
						   When isnumeric(substring([Address],@CharIxHouseNo+@StrLenHouseNo+1,6)) = 1 Then @StrLenHouseNo + 1 + len(rtrim(substring([Address],@CharIxHouseNo+@StrLenHouseNo+1,6)))
						   When isnumeric(substring([Address],@CharIxHouseNo+@StrLenHouseNo+1,5)) = 1 Then @StrLenHouseNo + 1 + len(rtrim(substring([Address],@CharIxHouseNo+@StrLenHouseNo+1,5)))
						   When isnumeric(substring([Address],@CharIxHouseNo+@StrLenHouseNo+1,4)) = 1 Then @StrLenHouseNo + 1 + len(rtrim(substring([Address],@CharIxHouseNo+@StrLenHouseNo+1,4)))
						   When isnumeric(substring([Address],@CharIxHouseNo+@StrLenHouseNo+1,3)) = 1 Then @StrLenHouseNo + 1 + len(rtrim(substring([Address],@CharIxHouseNo+@StrLenHouseNo+1,3)))
						   When isnumeric(substring([Address],@CharIxHouseNo+@StrLenHouseNo+1,2)) = 1 Then @StrLenHouseNo + 1 + len(rtrim(substring([Address],@CharIxHouseNo+@StrLenHouseNo+1,2)))
						   When isnumeric(substring([Address],@CharIxHouseNo+@StrLenHouseNo+1,1)) = 1 Then @StrLenHouseNo + 1 + len(rtrim(substring([Address],@CharIxHouseNo+@StrLenHouseNo+1,1)))
						   Else @StrLenHouseNo
	                     End
	 , @CharIxHouseNo = Case
	                      When @CountryCode in ( 'NL', 'NLD', 'DE', 'DEU' ) Then @CharIxHouseNo
						  When @CharIxHouseNo = 1 Then @CharIxHouseNo
						  When len(ltrim(rtrim(substring([Address],@CharIxHouseNo+@StrLenHouseNo,999)))) = 0 Then @CharIxHouseNo
						  Else 0 -- wanneer het adres in het buitenland niet met een nummer begint mag er niets meer achter het huisnummer komen
						End
	 , @StrLenHouseNo = Case When @CharIxHouseNo = 0 Then 0 Else @StrLenHouseNo End
	 , [Country_Region Code] = @CountryCode
	 , [Street] = Case
	                 When @CharIxHouseNo = 0 Then ltrim(rtrim([Address]))
	                 When @CharIxHouseNo = 1 Then 
	                   Case 
	                     When Left(ltrim(rtrim(substring([Address],@StrLenHouseNo+1,999))),1) = '-'
	                     Then substring(ltrim(rtrim(substring([Address],@StrLenHouseNo+1,999))),2,999)
	                     Else ltrim(rtrim(substring([Address],@StrLenHouseNo+1,999)))
	                   End
	                 Else 
	                   Case 
	                     When Right(ltrim(rtrim(substring([Address],1,@CharIxHouseNo-1))),1) = '-'
	                     Then ltrim(rtrim(substring([Address],1,@CharIxHouseNo-2)))
	                     Else ltrim(rtrim(substring([Address],1,@CharIxHouseNo-1)))
	                   End
	               End
	  , [House No_] = Case When @CharIxHouseNo = 0 Then '' Else substring([Address],@CharIxHouseNo,@StrLenHouseNo) End
	  , [House No_ Extension] = Case
	                              When @CharIxHouseNo = 0 Then ''
	                              When @CharIxHouseNo = 1 Then ''
	                              Else 
								    Case 
									  When Left(ltrim(rtrim(substring([Address],@CharIxHouseNo+@StrLenHouseNo,999))),1) in ( '-', '/' )
									  Then substring(ltrim(rtrim(substring([Address],@CharIxHouseNo+@StrLenHouseNo,999))),2,999)
									  Else ltrim(rtrim(substring([Address],@CharIxHouseNo+@StrLenHouseNo,999)))
									End
	                            End
	From @Adres a
	Join
	(          Select a.[ID]
			        , coalesce(napo.[Bron],sp.[Bron],tg.[Bron],'') as [Bron]
					, coalesce(napo.[positie vanaf],sp.[positie vanaf],tg.[positie vanaf]) as [positie vanaf]
					, coalesce(napo.[Eerste Cijfer],sp.[Eerste Cijfer],tg.[Eerste Cijfer],nr.[Eerste Cijfer]) as [Eerste Cijfer]
					, coalesce(napo.[Laatste Cijfer],sp.[Laatste Cijfer],tg.[Laatste Cijfer],nr.[Laatste Cijfer]) as [Laatste Cijfer]
	           From @Adres a
	           Left Join
	           ( -- adressen van militairen in het buitenland of op schepen
	             Select a.[ID]
	                  , 'NAPO' as [Bron]
	                  , 0  as [positie vanaf]
	                  , 0 as [Eerste Cijfer]
	                  , 0 as [Laatste Cijfer]
	             From @Adres a
	             Where a.[Post Code] in ( '3509VH', '3509VP', '3509VS', '3509 VH', '3509 VP', '3509 VS' )
	           ) napo on napo.[ID] = a.[ID]
	           Left Join 
	           ( Select x.[ID]
	                  , 'Special' as [Bron]
			          , MIN(x.[positie vanaf]) as [positie vanaf]
					  , MIN(x.[positie vanaf] + charindex([Cijfer],[Text After Tag])) - 1 as [Eerste Cijfer]
					  , MAX(x.[positie vanaf] + charindex([Cijfer],[Text After Tag])) - 1 as [Laatste Cijfer]
	             From
	             ( Select a.[ID]
	                    , substring([Address],charindex([zoekstring],lower([Address])) + len([zoekstring]),999) as [Text After Tag]
	                    , charindex([zoekstring],lower([Address])) + len([zoekstring]) as [positie vanaf]
	               From @Adres a
	               Join @Specials sp on sp.[Postcode] = a.[Post Code] and charindex([zoekstring],lower([Address])) > 0
	             ) x
	             Cross Join 
	             (  Select '0' as [Cijfer]
	                union Select '1' as [Cijfer]
	                union Select '2' as [Cijfer]
	                union Select '3' as [Cijfer]
	                union Select '4' as [Cijfer]
	                union Select '5' as [Cijfer]
	                union Select '6' as [Cijfer]
	                union Select '7' as [Cijfer]
	                union Select '8' as [Cijfer]
	                union Select '9' as [Cijfer]
	             ) c
				 Where charindex([Cijfer],[Text After Tag]) > 0 
				 Group By x.[ID]
	           ) sp on sp.[ID] = a.[ID]
			   Left Join
	           ( Select x.[ID]
	                  , 'Tag' as [Bron]
		          , MAX(x.[positie vanaf]) as [positie vanaf]
				  , MAX(x.[positie vanaf]) + MIN(charindex([Cijfer],[Text After Tag])) - 1 as [Eerste Cijfer]
					  , MAX(x.[positie vanaf] + charindex([Cijfer],[Text After Tag])) - 1 as [Laatste Cijfer]
	             From
	             ( Select a.[ID]
	                    , substring([Address],charindex([zoekstring],lower([Address])) + len([zoekstring]),999) as [Text After Tag]
	                    , charindex([zoekstring],lower([Address])) + len([zoekstring]) as [positie vanaf]
	               From @Adres a
	               Join @Tags t on charindex([zoekstring],lower([Address])) > 0
			   Where substring(lower([Address]),charindex([zoekstring],lower([Address])) + len([zoekstring]),1) not between 'a' and 'z'
	             ) x
	             Cross Join 
	             (  Select '0' as [Cijfer]
	                union Select '1' as [Cijfer]
	                union Select '2' as [Cijfer]
	                union Select '3' as [Cijfer]
	                union Select '4' as [Cijfer]
	                union Select '5' as [Cijfer]
	                union Select '6' as [Cijfer]
	                union Select '7' as [Cijfer]
	                union Select '8' as [Cijfer]
	                union Select '9' as [Cijfer]
	             ) c
				 Where charindex([Cijfer],[Text After Tag]) > 0 
				 Group By x.[ID]
	           ) tg on tg.[ID] = a.[ID]
			   Left Join
	           ( Select a.[ID]
					  , MIN(charindex([Cijfer],[Address])) as [Eerste Cijfer]
				  , MAX(LEN([Address]) - charindex([Cijfer],REVERSE([Address]))) + 1 as [Laatste Cijfer]
	             From @Adres a
	             Cross Join 
	             (  Select '0' as [Cijfer]
	                union Select '1' as [Cijfer]
	                union Select '2' as [Cijfer]
	                union Select '3' as [Cijfer]
	                union Select '4' as [Cijfer]
	                union Select '5' as [Cijfer]
	                union Select '6' as [Cijfer]
	                union Select '7' as [Cijfer]
	                union Select '8' as [Cijfer]
	                union Select '9' as [Cijfer]
	             ) c
				 Where charindex([Cijfer],[Address]) > 0 
				 Group By a.[ID]
	           ) nr on nr.[ID] = a.[ID]
	) x on x.[ID] = a.[ID]
	
	UPDATE tbl 
	SET tbl.[RetStreet Name] = CASE WHEN LEN(a.[House No_]) > 20 OR LEN(a.[House No_ Extension]) > 20 THEN a.[Address]
	                WHEN a.[Country_Region Code] IN ('NL','NLD','DE','DEU','BE','BEL','') THEN a.[Street] 
	                ELSE a.[Address] END,  
	    tbl.[RetHouse No_] =   CASE WHEN LEN(a.[House No_]) > 20 OR LEN(a.[House No_ Extension]) > 20 THEN ''
	                WHEN a.[Country_Region Code] IN ('NL','NLD','DE','DEU','BE','BEL','') THEN a.[House No_] 
	                  ELSE '' END,  
	    tbl.[RetHouse No_ Addition] = CASE WHEN LEN(a.[House No_]) > 20 OR LEN(a.[House No_ Extension]) > 20 THEN ''
	                          WHEN a.[Country_Region Code] IN ('NL','NLD','DE','DEU','BE','BEL','') THEN a.[House No_ Extension] 
	                         ELSE '' END  
	FROM [CompView_Contact] as tbl 
  JOIN @Adres AS a  
	  ON tbl.[No_] = a.[No_]  
	WHERE a.[Table No_] = 5050 
	 
	UPDATE tbl 
	SET tbl.[Street Name] = CASE WHEN LEN(a.[House No_]) > 20 OR LEN(a.[House No_ Extension]) > 20 THEN a.[Address]
	               WHEN a.[Country_Region Code] IN ('NL','NLD','DE','DEU','BE','BEL','') THEN a.[Street] 
	               ELSE a.[Address] END,  
	    tbl.[House No_] =   CASE WHEN LEN(a.[House No_]) > 20 OR LEN(a.[House No_ Extension]) > 20 THEN ''
	               WHEN a.[Country_Region Code] IN ('NL','NLD','DE','DEU','BE','BEL','') THEN a.[House No_] 
	               ELSE '' END,  
	    tbl.[House No_ Addition] = CASE WHEN LEN(a.[House No_]) > 20 OR LEN(a.[House No_ Extension]) > 20 THEN ''
	                         WHEN a.[Country_Region Code] IN ('NL','NLD','DE','DEU','BE','BEL','') THEN a.[House No_ Extension] 
	                      ELSE '' END  
	FROM [CompView_RetRetail Customer] as tbl 
  JOIN @Adres AS a  
	  ON tbl.[No_] = a.[No_]  
	WHERE a.[Table No_] = 11019278 
	 
	UPDATE tbl 
	SET tbl.[RetStreet Name] = CASE WHEN LEN(a.[House No_]) > 20 OR LEN(a.[House No_ Extension]) > 20 THEN a.[Address]
	                WHEN a.[Country_Region Code] IN ('NL','NLD','DE','DEU','BE','BEL','') THEN a.[Street] 
	                ELSE a.[Address] END,  
	    tbl.[RetHouse No_] =   CASE WHEN LEN(a.[House No_]) > 20 OR LEN(a.[House No_ Extension]) > 20 THEN ''
	                WHEN a.[Country_Region Code] IN ('NL','NLD','DE','DEU','BE','BEL','') THEN a.[House No_] 
	                  ELSE '' END,  
	    tbl.[RetHouse No_ Addition] = CASE WHEN LEN(a.[House No_]) > 20 OR LEN(a.[House No_ Extension]) > 20 THEN ''
	                          WHEN a.[Country_Region Code] IN ('NL','NLD','DE','DEU','BE','BEL','') THEN a.[House No_ Extension] 
	                         ELSE '' END  
	FROM [CompView_Vendor] as tbl 
  JOIN @Adres AS a  
	  ON tbl.[No_] = a.[No_]  
	WHERE a.[Table No_] = 23 
	 
	UPDATE tbl 
	SET tbl.[RetStreet Name] = CASE WHEN LEN(a.[House No_]) > 20 OR LEN(a.[House No_ Extension]) > 20 THEN a.[Address]
	                WHEN a.[Country_Region Code] IN ('NL','NLD','DE','DEU','BE','BEL','') THEN a.[Street] 
	                ELSE a.[Address] END,  
	    tbl.[RetHouse No_] =   CASE WHEN LEN(a.[House No_]) > 20 OR LEN(a.[House No_ Extension]) > 20 THEN ''
	                WHEN a.[Country_Region Code] IN ('NL','NLD','DE','DEU','BE','BEL','') THEN a.[House No_] 
	                  ELSE '' END,  
	    tbl.[RetHouse No_ Addition] = CASE WHEN LEN(a.[House No_]) > 20 OR LEN(a.[House No_ Extension]) > 20 THEN ''
	                          WHEN a.[Country_Region Code] IN ('NL','NLD','DE','DEU','BE','BEL','') THEN a.[House No_ Extension] 
	                         ELSE '' END  
	FROM [CompView_Bank Account]as tbl 
  JOIN @Adres AS a  
	  ON tbl.[No_] = a.[No_]  
	WHERE a.[Table No_] = 270 
	 
	UPDATE tbl 
	SET tbl.[RetStreet Name] = CASE WHEN LEN(a.[House No_]) > 20 OR LEN(a.[House No_ Extension]) > 20 THEN a.[Address]
	                WHEN a.[Country_Region Code] IN ('NL','NLD','DE','DEU','BE','BEL','') THEN a.[Street] 
	                ELSE a.[Address] END,  
	    tbl.[RetHouse No_] =   CASE WHEN LEN(a.[House No_]) > 20 OR LEN(a.[House No_ Extension]) > 20 THEN ''
	                WHEN a.[Country_Region Code] IN ('NL','NLD','DE','DEU','BE','BEL','') THEN a.[House No_] 
	                  ELSE '' END,  
	    tbl.[RetHouse No_ Addition] = CASE WHEN LEN(a.[House No_]) > 20 OR LEN(a.[House No_ Extension]) > 20 THEN ''
	                          WHEN a.[Country_Region Code] IN ('NL','NLD','DE','DEU','BE','BEL','') THEN a.[House No_ Extension] 
	                         ELSE '' END  
	FROM [CompView_Customer] as tbl 
  JOIN @Adres AS a  
	  ON tbl.[No_] = a.[No_]  
	WHERE a.[Table No_] = 18 
	
END	
