How to search a list on multiple columns?-CodePudding

I have a temp table with multiple items.

I want to search multiple columns in a second table to get the rows which has this items but with the LIKE Function:

For example

#list = temp table with items 

SELECT *
FROM table2 
WHERE 
    ID like %#list%
 OR Name like %#list%
 OR Adress like %#list%

Is this possible with TSQL?

CodePudding user response：

I would suggest you use an EXISTS:

SELECT *
FROM table2 t2
WHERE EXISTS (SELECT 1
              FROM #List L
              WHERE t2.ID LIKE CONCAT('%',L.Item,'%') --Assumes ID is a string based data type
                 OR t2.Name LIKE CONCAT('%',L.Item,'%')
                 OR t2.Adress LIKE CONCAT('%',L.Item,'%')); --Address has 2 d's

CodePudding user response：

I found a good answer.

Put the strings you want to look after in a #temp table with '%text1%'

items
%text1%
%text2%

Then use JOINs for searching like:

SELECT *
FROM table_1
INNER JOIN #temp
  ON table_1.column1 like #temp.items
  OR table_1.column2 like #temp.items

...

I think it works well