SQL Server 2008 R2 – Find Rows Containing All Keywords Across Multiple Columns

sql serversql-server-2008-r2

I'm trying to create a universal search functionality. The goal is to compare the values of multiple columns and see if all of the provided keywords appear in those columns. For various reasons, using a Full Text Index and CONTAINS is not an option, nor is utilizing a CLR function. Are there any other options to solve this problem?

Example:

----------------------------------------------------
|    Row    |    Col1    |    Col2    |    Col3    |
----------------------------------------------------
|     1     |    Foo     |    Bar     |    Stack   |
----------------------------------------------------
|     2     |    Foo     |    Blitz   |    Stack   |
----------------------------------------------------
|     3     |    Foo     |     Foo    |     Bar    |
----------------------------------------------------

Search text "Foo Bar" matches Rows 1 and 3.

Search text "Foo Stack" matches Rows 1, and 2.

Search Text "Foo" matches Rows 1, 2 and 3.

Search text "Foo Bar Stack Blitz" matches no rows.

Search text "Foo Blitz" matches Row 2.

Best Answer

You can use a string splitter and do this. I use Jeff Moden's splitter but you can use your favorite. This essentially splits the string on the space, and then does a FULL JOIN and only returns rows where there is a mach for each word. This is handy since it doesn't care the order of the words in the string. i.e. Foo Bar is the same as Bar Foo.

SQL FIDDLE

declare @table table ([Row] int identity (1,1), Col1 char(3), Col2 varchar(6), Col3 varchar(6))
insert into @table
values

('Foo','Bar','Stack'),
('Foo','Blitz','Stack'),
('Foo','Foo','Bar')


declare @search varchar(64)

set @search = 'Foo Bar'

select distinct
    t.*
from @table t
full join 
    dbo.DelimitedSplit8K(@search,' ') s on
    s.Item = t.Col1
    or s.Item = t.Col2
    or s.Item = t.Col3
where
    t.Row is not null
group by
    t.Row
    ,t.Col1
    ,t.Col2
    ,t.Col3
having
    count(*) = (select max(ItemNumber) from dbo.DelimitedSplit8K(@search,' '))

Related Solutions

Sql-server – Full-text search in SQL Server on multiple tables fails

Hi we can query multiple tables in a full-text search by using views ,but we some advantages and disadvantages . Here in your case you created view dbo.vw_recipe_search it seems your are not using it in altered procedure when i query it i am getting results as per intended

  SELECT *
        FROM dbo.vw_recipe_search r 
        INNER JOIN FREETEXTTABLE(vw_recipe_search, *, 'salsa peas') kt
            ON r.search_id = kt.[KEY]
        ORDER BY RANK DESC;

The problem in this approach is ranking and weightage of each column , like suppose if u want to give high weight to title column than descrip you have to do a lot of work in order by rank column . We used Levenstein string comparision function to order for better results set

Sql-server – How to find all positions of a string within another string

declare @name nvarchar(max)
set @name ='ali reza dar yek shabe barani ba yek  dokhtare khoshkel be disco raft va ali baraye 1 saat anja bud va sepas... ali...'

Declare @a table (pos int)
Declare @pos int
Declare @oldpos int
Select @oldpos=0
select @pos=patindex('%ali%',@name) 
while @pos > 0 and @oldpos<>@pos
 begin
   insert into @a Values (@pos)
   Select @oldpos=@pos
   select @pos=patindex('%ali%',Substring(@name,@pos + 1,len(@name))) + @pos
end

Select * from @a

To make it reuseable you can use it in a table function to call it like:

Select * from  dbo.F_CountPats ('ali reza dar yek shabe barani ba yek  dokhtare khoshkel be disco raft va ali baraye 1 saat anja bud va sepas... ali...','%ali%')

The function could look like this

Create FUNCTION [dbo].[F_CountPats] 
(
@txt varchar(max),
@Pat varchar(max)
)
RETURNS 
@tab TABLE 
(
 ID int
)
AS
BEGIN
Declare @pos int
Declare @oldpos int
Select @oldpos=0
select @pos=patindex(@pat,@txt) 
while @pos > 0 and @oldpos<>@pos
 begin
   insert into @tab Values (@pos)
   Select @oldpos=@pos
   select @pos=patindex(@pat,Substring(@txt,@pos + 1,len(@txt))) + @pos
end

RETURN 
END

GO

Best Answer

Related Solutions

Sql-server – Full-text search in SQL Server on multiple tables fails

Sql-server – How to find all positions of a string within another string

Related Question