Using JOIN statement with CONTAINS function

Question

In SQL Server database I have a View with a lot of INNER JOINs statements. The last join uses LIKE predicate and that's why it's working too slowly. The query looks like :

SELECT *
FROM A INNER JOIN
B ON A.ID = B.ID INNER JOIN
C ON C.ID1 = B.ID1 INNER JOIN
...........................
X ON X.Name LIKE '%' + W.Name  + '%' AND
            LIKE '%' + W.Name2  + '%' AND
            LIKE '%' + W.Name3  + '%'

I want to use CONTAINS instead of LIKE as :

SELECT *
FROM A INNER JOIN
B ON A.ID = B.ID INNER JOIN
C ON C.ID1 = B.ID1 INNER JOIN
...........................
X ON CONTAINS(X.Name, W.Name) AND
     CONTAINS(X.Name, W.Name2) AND
     CONTAINS(X.Name, W.Name3)

I know that CONTAINS is working faster than LIKE and also that can't use CONTAINS in JOIN statements. Is there any workaround in this case or suggestion? Thanks in advance.

Pantia

SELECT * FROM tableA AS_a INNER JOIN tableB AS b ON b.field LIKE CONCAT('%', CONCAT(a.field, '%')); – Peery

Answer 1

It's not that CONTAINS can't be used in joins.

You just can't use columns as a second parameter of CONTAINS - see MSDN - CONTAINS (Transact-SQL)

CONTAINS
( { column_name | ( column_list ) | * } 
  ,'<contains_search_condition>'     
[ , LANGUAGE language_term ]
) 

However, you can use a variable as a search condition, so you can use a cursor and then get all data you need. Here is some very rough example:

declare @Name nvarchar(max)

declare @Temp_A table(Name nvarchar(max))
declare @Temp_B table(Name nvarchar(max))

--=============================================================================================
insert into @Temp_A (Name)
select 'Test'

insert into @Temp_B (Name)
select 'aaaTestaaa'

--=============================================================================================
-- Query 1 - LIKE
--=============================================================================================
select *
from @Temp_A as A
    inner join @Temp_B as B on B.Name like '%' + A.Name + '%'

--=============================================================================================
-- Query 2 - CONTAINS
--=============================================================================================
declare table_cursor cursor local fast_forward for
    select distinct Name from @Temp_A
open table_cursor
while 1 = 1
begin
    fetch table_cursor into @Name
    if @@fetch_status <> 0 break

    select * from @Temp_B where contains(Name, @Name)
end
close table_cursor
deallocate table_cursor

Doloresdolorimetry

Thanks for reply. I'm not familiar with cursor. Could you please give me an example, how to use it in my case? – Pantia

Added some examples (it's very approximate, though) – Doloresdolorimetry

Answer 2

CONCAT works perfect, I have tested it with PostgreSQL

SELECT *
FROM TABLE_ONE AS a INNER JOIN TABLE_TWO AS b
    ON b.field LIKE CONCAT('%', CONCAT(a.field, '%'));

Please refer to similar answer here

Peery

I don't know why but this is the only solution that works for me, other solutions for joining ON a substring are not working for some reason. So thank you. – Schwarzwald

Answer 3

You can create a join using a LIKE.. something like this:

SELECT * FROM TABLE_ONE 
FULL OUTER JOIN TABLE_TWO ON TABLE_ONE.String_Column LIKE '%' + TABLE_TWO.Name + '%'

ie - select everything from TABLE_ONE where the string_column is contained in the TABLE_TWO name

Ecumenical

Answer 4

In short there isn't a way to do this using CONTAINS, it simply is not allowed in a JOIN like this. see: TSQL - A join using full-text CONTAINS

So although there is performance hit, IMO like is the easiest solution here.

Olympe