CODEWITHSUNDEEP
sql-serversql-server-2008-r2full-text-search
MAK
MAK

Reputation: 7260

Full text search using CONTAINS

I have the following records:

create table ft_test1
(
    col1 int identity(1,1),
    col2 varchar(max)
);

insert into ft_test1 values('John A Henry');
insert into ft_test1 values('Dsouza mak Dee');
insert into ft_test1 values('Smith Ashla Don');

create table ft_test2
(
    col1 int identity(1,1),
    col2 varchar(max)
);

insert into ft_test2 values('Mak');
insert into ft_test2 values('McKoy Obee Zen');
insert into ft_test2 values('Henry A John');

FULLTEXT INDEX Setup:

Step 1: Creating unique index

CREATE UNIQUE INDEX UIX_test1_Col1 ON ft_test1(col1);
CREATE UNIQUE INDEX UIX_test2_Col1 ON ft_test2(col1);

Step 2: Creating Catalog 

CREATE FULLTEXT CATALOG cat_ft
WITH ACCENT_SENSITIVITY = OFF

Step 3: Creating FULLTEXT INDEX

CREATE FULLTEXT INDEX ON ft_test1  
(col2 LANGUAGE 1033)  
KEY INDEX UIX_test1_Col1  
ON cat_ft  
WITH STOPLIST = SYSTEM

CREATE FULLTEXT INDEX ON ft_test2  
(col2 LANGUAGE 1033)  
KEY INDEX UIX_test2_Col1  
ON cat_ft  
WITH STOPLIST = SYSTEM

Query: I want to do JOIN between these two tables by matching the col2 values and return table ft_test1 values.

Expected Output:

col2
------------
Dsouza mak Dee
John A Henry

Explaination about expected output:

  1. The first record should display because of mak present in both table in col2
  2. The second record is also present but with shuffling.

Try:

SELECT t1.col2
FROM ft_test1 t1
INNER JOIN ft_test2 t2
ON CONTAINS(t2.col2, t1.col2);

Error:

Msg 102, Level 15, State 1, Line 4 Incorrect syntax near 't1'.

Upvotes: 2

Views: 90

Answers (2)

level3looper
level3looper

Reputation: 1041

Using ft_test2 to build a search term:

Declare @vals varchar(1000) = '';
Select @Vals = @Vals + Case When @Vals = '' Then '"'+col2+'"' Else ' OR ' + '"'+col2+'"' End
From ft_test2;

Select * From ft_test1  where FreeText(col2, @Vals);

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453278

CONTAINS doesn't do what you want. FREETEXT is closer and returns your desired results in this case.

You should check the documentation to see if the complete semantics are acceptable.

You would also need a helper TVF as below.

CREATE FUNCTION F1(@SearchText VARCHAR(8000))
RETURNS @ft_test2 TABLE (
  col1 INT,
  col2 VARCHAR(max))
AS
  BEGIN
      INSERT INTO @ft_test2
      SELECT *
      FROM   ft_test2 t2
      WHERE  FREETEXT (t2.col2, @SearchText)

      RETURN
  END

GO

SELECT t1.col2
FROM   ft_test1 t1
       CROSS APPLY F1(t1.col2)

Upvotes: 3

Related Questions