contains search over a table variable or a temp table

Question

i'm trying to concatenate several columns from a persistent table into one column of a table variable, so that i can run a contains("foo" and "bar") and get a result even if foo is not in the same column as bar.

however, it isn't possible to create a unique index on a table variable, hence no fulltext index to run a contains.

is there a way to, dynamically, concatenate several columns and run a contains on them? here's an example:

declare @t0 table
(
    id uniqueidentifier not null,
    search_text varchar(max)
)

declare @t1 table ( id uniqueidentifier )

insert into
    @t0 (id, search_text)
select
    id,
    foo + bar
from
    description_table

insert into
    @t1
select
    id
from
    @t0
where
    contains( search_text, '"c++*" AND "programming*"' )

Answer 1

declare @table table
(
    id int,
    fname varchar(50)
)

insert into @table select 1, 'Adam James Will'
insert into @table select 1, 'Jain William'
insert into @table select 1, 'Bob Rob James'

select * from @table where fname like '%ja%' and fname like '%wi%'

Is it something like this.

Answer 2

You cannot use CONTAINS on a table that has not been configured to use Full Text Indexing, and that cannot be applied to table variables.

If you want to use CONTAINS (as opposed to the less flexible PATINDEX) you will need to base the whole query on a table with a FT index.

Answer 3

You can't use full text indexing on a table variable but you can apply the full text parser. Would something like this do what you need?

declare @d table
(
id int identity(1,1),
testing varchar(1000)
)

INSERT INTO @D VALUES ('c++ programming')
INSERT INTO @D VALUES ('c# programming')
INSERT INTO @D VALUES ('c++ books')


SELECT id
FROM @D
CROSS APPLY sys.dm_fts_parser('"' + REPLACE(testing,'"','""') + '"', 1033, 0,0)
where display_term in ('c++','programming')
GROUP BY id
HAVING COUNT(DISTINCT display_term)=2

NB: There might well be a better way of using the parser but I couldn't quite figure it out. Details of it are at this link