SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
	declare	@keyWord varchar(255)
	SET @keyWord = 'tester?de?tester?de?test'
	declare @R Table(
		id int
	)
	-- Table to contain the keyWords
	declare @keyWords Table(
		rowNr tinyint identity (1,1),
		word varchar(50)
	)
	-- Temp table
	declare @T Table(
		id int
	)
-- Initialist the keywords table (parse parameter, sort on len)
	INSERT INTO @keyWords (word)
	SELECT distinct keyWord as word
	from splitString(@keyWord, '?')
	order by word desc
select *  from @keyWords
	-- Initialise the counter on 1
	declare @i int
	set @i=1
	-- Initialze the current keyword
	select @keyWord = word
		from @keyWords
		where rowNr = @i
print (@keyWord)
	-- Initialize the result table with results from current keyword
	insert into @R (id)
		SELECT distinct clr.clr_id
		FROM	Clr
		LEFT JOIN Co ON Co.Co_ID = Clr.Co_ID
						AND Co.Long_Nm LIKE '%' + @Keyword + '%'
		LEFT JOIN Clr_Nm ON Clr_Nm.Clr_ID = Clr.Clr_ID
						AND Clr_Nm.Clr_Nm LIKE '%' + @Keyword + '%'
		LEFT JOIN Clr_Use_Yr ON Clr_Use_Yr.Clr_ID = Clr.Clr_ID
						AND Clr_Use_Yr.Yr_Num LIKE '%' + @Keyword + '%'
		LEFT JOIN Modl ON Modl.Modl_ID = Clr_Use_Yr.Modl_ID
						AND Modl.Modl_Nm LIKE '%' + @Keyword + '%'
		LEFT JOIN Paint_Cd ON Paint_Cd.Clr_ID = Clr.Clr_ID
						AND Paint_Cd.Paint_Cd LIKE '%' + @Keyword + '%'
		WHERE Paint_Cd.Clr_ID IS NOT NULL
					OR Modl.Modl_ID IS NOT NULL
					OR Clr_Use_Yr.Clr_ID IS NOT NULL
					OR Clr_Nm.Clr_ID IS NOT NULL
					OR Co.Co_ID IS NOT NULL
select * from @R	
	-- Increment counter
	SET @i  = @i + 1
	-- As long as the result table can be filtered further down and there are words to filter it on
	while (@i <= (select count(*) from @keyWords) AND (select count(*) from @R) > 0)
	BEGIN
		-- Set the current keyword
		select @keyWord = word
			from @keyWords
			where rowNr = @i
		--Fill the temp table with the filtered results
		insert into @T (id)
			select distinct c.clr_id
			from clr c
			inner join @R r	on r.id = c.clr_id
			inner join clr_use_yr tmp on c.clr_id = tmp.clr_id
			left join modl m on m.modl_id = tmp.modl_id
				AND m.modl_nm LIKE '%' + @Keyword + '%'
			Left JOIN Clr_Use_Yr cuy ON cuy.Clr_ID = c.Clr_ID
							AND cuy.Yr_Num LIKE '%' + @Keyword + '%'
			LEFT JOIN Co ON Co.Co_ID = c.Co_ID
							AND Co.Long_Nm LIKE '%' + @Keyword + '%'
			LEFT JOIN Paint_Cd pc ON pc.Clr_ID = c.Clr_ID
							AND pc.Paint_Cd LIKE '%' + @Keyword + '%'
			LEFT OUTER JOIN Clr_Nm cn ON cn.Clr_ID = c.Clr_ID
							AND cn.Clr_Nm LIKE '%' + @Keyword + '%'
			WHERE m.modl_nm IS NOT NULL
					OR cuy.Yr_num IS NOT NULL
					OR Co.Long_nm IS NOT NULL
					OR pc.Paint_cd IS NOT NULL
					OR cn.Clr_nm IS NOT NULL
		-- Put the results in the final table
		DELETE @R
		INSERT INTO @R (id)
			select t.id from @T t
		-- Clean the temp result
		DELETE @T
		-- Increment counter
		SET @i  = @i + 1
	END
	RETURN 
GO
select * from GetcommonSearchResultForAlt16DelimitedStrings('mercedes?blue?1984')