-2

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,',')
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
rick_87394
  • 1
  • 3

1 Answers1

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*"
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Hello, thank you for the answer, but I have realized that this query is based on a View and it is not fully indexed and I can't make it because the error says View is not schema bound. Threfore I have to change it to answer on this link : https://stackoverflow.com/questions/1865353/combining-like-and-in-for-sql-server which has to create the multiple LIKE/OR Clause. I am working to create it based on your answer, but if you can help it would be great. – rick_87394 Jan 21 '22 at 09:47
  • @rick not sure I follow. Are you saying you’re not using fulltext search and so you should not have asked how to generate a CONTAINS clause? – Aaron Bertrand Jan 21 '22 at 10:57
  • I didn't check thoroughly that the query was running on a view, sorry, my mistake. I have asked another question and it was answered there by the way, thanks. – rick_87394 Jan 21 '22 at 11:49