create  procedure SearchSample (@SearchString varchar(200), @MatchType int = 0)
as

-- SeachString:  String of 1 or more search terms, all seperated by spaces
-- MatchType: 0=match any, 1 =match all, 2 = exact match of entire expression only

-- 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.*
from
	SomeTable T
inner join
	(	
	select 
		ID, Count(*)  * 1.0 / @WordCount as MatchPct
	from 
		SomeTable T
	inner join 
		@Words W
	on 
		' ' + T.SomeColumn + ' ' like '%[^a-z]' + Word + '[^a-z]%' 
	group by 
		ID
	) a
on
	T.ID = a.ID
where
	MatchPct = 1 or @MatchType <>1
order by
	MatchPct