-- Results are returned in order of relevance

 

declare @i1 int;
declare @i2 int;

declare @Word varchar(100);
declare @Words table (Word varchar(200) not null);
declare @WordCount as integer;

set nocount on
-- Parse the SearchString to extract all words:
if (@MatchType != 2)
	begin
	set @SearchString = ' ' + @SearchString  + ' ';
	set @i1 = 1;
	while (@i1 != 0)
	begin

    set @i2=charindex(' ', @SearchString, @i1+1)
		if (@i2 != 0)
		begin
		set @Word = rtrim(ltrim(substring(@SearchString, @i1+1, @i2-@i1)))
		if @Word != '' insert into @Words select @Word
	end

    set @i1 = @i2
	end
	end
else
	insert into @Words select ltrim(rtrim(@SearchString))

-- Get the total # of words:
set @WordCount = (select count(*) from @Words)

-- Return Results in order of relevance:
select 

  a.MatchPct, T.*,
productimages.isprimary,
T.productname + ' (' + T.productcode + ') ' AS test,
 ISNULL('uploads/products/p' + CONVERT (varchar, productimages.productid) + '/' + productimages.imagename, 'images/noimage.gif') AS imagename

from

  products T
LEFT OUTER JOIN productimages ON T.productid = productimages.productid
inner join

  ( 
	select 
	productcode,  productname, productid,  Count(*)  * 1.0 / @WordCount as MatchPct
	from 
	products T
	inner join 

    @Words W on ' ' + T.productname + ' ' + T.productcode + ' ' like '%[^a-z]' + Word + '[^a-z]%' 

  group by 

   productname, productid , productcode

  ) a on T.productid = a.productid

where

  MatchPct = 1 and productimages.isprimary = 1 OR productimages.isprimary IS NULL
or @MatchType <>1 and productimages.isprimary = 1 OR productimages.isprimary IS NULL

order by

  MatchPct

DECLARE	@Products TABLE (ProductCode VARCHAR(20), ProductName VARCHAR(80))

INSERT	@Products
SELECT	'son-dbc-32', 'Sony Digital Betacam 32' UNION ALL
SELECT	'son-bct-32', 'Sony Betacam SP 32'

DECLARE	@SearchString VARCHAR(80)

SET	@SearchString = 'son dbc 32'

;WITH Yak (ProductCode, ProductName, Hit)
AS (
	SELECT		p.ProductCode,
			p.ProductName,
			MAX(CASE WHEN pSearch.Data IN (pCode.Data, pName.Data) THEN 1 ELSE 0 END)
	FROM		@Products AS p
	CROSS APPLY	dbo.fnParseList('-', REPLACE(p.ProductCode, ' ', '-')) AS pCode
	CROSS APPLY	dbo.fnParseList('-', REPLACE(p.ProductName, ' ', '-')) AS pName
	CROSS APPLY	dbo.fnParseList('-', REPLACE(@SearchString, ' ', '-')) AS pSearch
	GROUP BY	p.ProductCode,
			p.ProductName,
			pCode.Data,
			pName.Data
), Search (ProductCode, ProductName, Pct)
AS (
	SELECT		ProductCode,
			ProductName,
			CONVERT(DECIMAL(5, 2), 100.0 * SUM(Hit) / COUNT(*)) AS Pct
	FROM		Yak
	GROUP BY	ProductCode,
			ProductName
)

SELECT		s.ProductCode,
		s.ProductName,
		s.Pct
FROM		Search AS s
WHERE		s.Pct > 66.66
ORDER BY	Pct DESC,
		s.ProductCode,
		s.ProductName

Msg 208, Level 16, State 1, Line 7
Invalid object name 'dbo.fnParseList'.

CREATE FUNCTION dbo.fnParseString
(
	@Section SMALLINT,
	@Delimiter CHAR,
	@Text VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS

BEGIN
	DECLARE	@NextPos SMALLINT,
		@LastPos SMALLINT,
		@Found SMALLINT
	
	SELECT	@NextPos = 0,
		@Found = 0
	
	IF @Section > 0
		SELECT	@Text = REVERSE(@Text)
	
	WHILE @NextPos <= DATALENGTH(@Text) AND @Found < ABS(@Section)
		SELECT	@LastPos = @NextPos,
			@NextPos =	CASE
						WHEN CHARINDEX(@Delimiter, @Text, @LastPos + 1) = 0 THEN DATALENGTH(@Text) + 1
						ELSE CHARINDEX(@Delimiter, @Text, @LastPos + 1)
					END,
			@Found = @Found + 1
	
	IF @Found <> ABS(@Section) OR @Found = 0 OR @Section = 0
		SELECT	@Text = NULL
	ELSE
		SELECT	@Text = SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1)
	
	RETURN CASE WHEN @Section < 0 THEN @Text ELSE REVERSE(@Text) END
END

DECLARE	@Products TABLE (ProductCode VARCHAR(20), ProductName VARCHAR(80))

INSERT	@Products
SELECT	'son-dbc-32', 'Sony Digital Betacam 32' UNION ALL
SELECT	'son-bct-32', 'Sony Betacam SP 32'

DECLARE	@SearchString VARCHAR(80)

SET	@SearchString = 'son dbc 32'

;WITH Yak (ProductCode, ProductName, Hit)
AS (
	SELECT		p.ProductCode,
			p.ProductName,
			MAX(CASE WHEN pSearch.Data IN (pCode.Data, pName.Data) THEN 1 ELSE 0 END)
	FROM		@Products AS p
	CROSS APPLY	dbo.fnParseList('-', REPLACE(p.ProductCode, ' ', '-')) AS pCode
	CROSS APPLY	dbo.fnParseList('-', REPLACE(p.ProductName, ' ', '-')) AS pName
	CROSS APPLY	dbo.fnParseList('-', REPLACE(@SearchString, ' ', '-')) AS pSearch
	GROUP BY	p.ProductCode,
			p.ProductName,
			pCode.Data,
			pName.Data
), Search (ProductCode, ProductName, Pct)
AS (
	SELECT		ProductCode,
			ProductName,
			CONVERT(DECIMAL(5, 2), 100.0 * SUM(Hit) / COUNT(*)) AS Pct
	FROM		Yak
	GROUP BY	ProductCode,
			ProductName
)

SELECT		s.ProductCode,
		s.ProductName,
		s.Pct
FROM		Search AS s
WHERE		s.Pct > 66.66
ORDER BY	Pct DESC,
		s.ProductCode,
		s.ProductName

(2 row(s) affected)
Msg 208, Level 16, State 1, Line 11
Invalid object name 'dbo.fnParseList'.

CREATE FUNCTION dbo.fnParseList
(
	@Delimiter CHAR,
	@Text TEXT
)
RETURNS @Result TABLE (RowID SMALLINT IDENTITY(1, 1) PRIMARY KEY, Data VARCHAR(8000))
AS

BEGIN
	DECLARE	@NextPos INT,
		@LastPos INT

	SELECT	@NextPos = CHARINDEX(@Delimiter, @Text, 1),
		@LastPos = 0

	WHILE @NextPos > 0
		BEGIN
			INSERT	@Result
				(
					Data
				)
			SELECT	SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1)

			SELECT	@LastPos = @NextPos,
				@NextPos = CHARINDEX(@Delimiter, @Text, @NextPos + 1)
		END

	IF SCOPE_IDENTITY() > 0
		INSERT	@Result
			(
				Data
			)
		SELECT	SUBSTRING(@Text, @LastPos + 1, DATALENGTH(@Text) - @LastPos)

	RETURN
END