Contains search by table variable or temporary table

Question

I am trying to combine several columns from a constant table into one column of a table variable so that I can run contains ("foo" and "bar") and get the result, even if foo is not in the same column as the bar.

however, it is not possible to create a unique index for a table variable, so the full index to run a does not contain.

Is there a way to dynamically merge multiple columns and run them on them? here is 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

You cannot use CONTAINS in a table that has not been configured to use full-text indexing, and which 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 entire query on a table with an FT index.

Answer 2

You cannot use full-text indexing for a table variable, but you can use a full text parser. Will 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: Perhaps the best way to use the parser, but I could not figure it out. Detailed information about him at this link

Answer 3

 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%' 

It is something like this.