Search using LIKE operator with multiple dynamic values accepting both full and partial text match

Is there any way to do multiple term search in a column using like operator dynamically in SQL Server? Like below

SELECT ID 
FROM table 
WHERE 
   Company LIKE '%goog%' OR 
   Company LIKE '%micros%'  OR 
   Company LIKE '%amazon%' 

For example: input values “goog; micro; amazon;” (input value should auto split by delimiter ‘;’ and check the text exist in the table) means that Search term ‘goog’ or ‘micros’ or ‘amazon’ from company column, if exists return.

Table – sample data:

ID                 Company 
------------------------------------------
1                  Google; Microsoft; 
2                  oracle; microsoft; apple; walmart; tesla
3                  amazon; apple;
4                  google;
5                  tesla;        
6                  amazon;

Basically, The above query should return the results as like below,

Desired results:

ID                 
-----   
1
2                                    
4   
6               

Is it possible to achieve in SQL Server by splitting, then search in query? I look forward to an experts answer.

Answer

If you pass in a table valued parameter, you can join on that.

So for example

CREATE TYPE StringList AS TABLE (str varchar(100));

DECLARE @tmp StringList;
INSERT @tmp (str)
VALUES
('%goog%'),
('%micros%'),
('%amazon%');

SELECT t.ID 
FROM table t
WHERE EXISTS (SELECT 1
   FROM @tmp tmp
   WHERE t.Company LIKE tmp.str);

The one issue with this is that someone could write le; Mic and still get a result.

Strictly speaking, your table design is flawed, because you are storing multiple different items in the same column. You really should have this normalized into rows, so every Company is a separate row. Then your code would look like this:

SELECT t.ID 
FROM table t
JOIN @tmp tmp ON t.Company LIKE tmp.str
GROUP BY t.ID

You can simulate it by splitting your string

SELECT t.ID 
FROM table t
WHERE EXISTS (SELECT 1
   FROM STRING_SPLIT(t.Company) s
   JOIN @tmp tmp ON s.value LIKE tmp.str);