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,
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.
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);