How to dynamically write OR clause in Contains query?

Question

Is there a way to generate the second part of this SQL query (OR clauses) from a string split?

WHERE CONTAINS(t.something, '"bla*" OR "foo*" OR "batz*"')

String split values come from :

DECLARE @String_With_Commas varchar(max);
SET @String_With_Commas = 'Mercedes, BMW, Audi, Tesla, Land Rover';

select value from string_split(@String_With_Commas,',')

Answer 1

You can split the string using STRING_SPLIT(), then rebuild it using STRING_AGG(), and simply pass the resulting string into CONTAINS:

DECLARE @String_With_Commas nvarchar(255);
SET @String_With_Commas = N'Mercedes, BMW, Audi, Tesla, Land Rover';

DECLARE @SearchPhrase nvarchar(max);

SELECT @SearchPhrase = CONCAT(N'"', 
  STRING_AGG(LTRIM(value), N'*" OR "'), N'*"')
  FROM STRING_SPLIT(@String_With_Commas, N',') AS s;

SELECT ...
WHERE CONTAINS(t.something, @SearchPhrase);

Or even simpler:

DECLARE @String_With_Commas nvarchar(255);
SET @String_With_Commas = N'Mercedes, BMW, Audi, Tesla, Land Rover';

DECLARE @SearchPhrase nvarchar(max)
 = CONCAT(N'"', REPLACE(@String_With_Commas, N', ', N'*" OR "'), '*"');

SELECT ...
WHERE CONTAINS(t.something, @SearchPhrase);

In both cases, @SearchPhrase looks like this:

"Mercedes*" OR "BMW*" OR "Audi*" OR "Tesla*" OR "Land Rover*"