Combining this with
your other question[
^], something like this should work - although it probably won't be particularly quick:
DECLARE @sql1 varchar(max) = '...';
SET @sql1 = REPLACE(@sql1, '\"', '"');
SET @sql1 = REPLACE(@sql1, ' OR ', '|');
DECLARE @words TABLE (word varchar(4000) NOT NULL);
INSERT INTO @words (word) SELECT DISTINCT value FROM STRING_SPLIT(@sql1, '|');
DECLARE @matches TABLE
(
[key] int NOT NULL,
[rank] int NOT NULL,
MatchedWords varchar(max) NOT NULL
);
DECLARE @word varchar(4000);
WHILE EXISTS(SELECT 1 FROM @words)
BEGIN
SELECT TOP 1 @word = word FROM @words;
DELETE FROM @words WHERE word = @word;
MERGE
@matches As M
USING CONTAINSTABLE([Libro_BOE - Código de Derecho Agroalimentario (Contexto Sectorial de la Industria Agroalimentaria)], [Contenido], @word) As FT
ON M.[key] = FT.[key]
WHEN MATCHED THEN
UPDATE
SET
[rank] = CASE WHEN FT.[rank] > M.[rank] THEN FT.[rank] ELSE M.[rank] END,
MatchedWords = MatchedWords + ', ' + @word
WHEN NOT MATCHED BY TARGET THEN
INSERT
(
[key],
[rank],
MatchedWords
)
VALUES
(
[key],
[rank],
@word
)
;
END;
SELECT
T.*,
FT.*
FROM
[Libro_BOE - Código de Derecho Agroalimentario (Contexto Sectorial de la Industria Agroalimentaria)] As T
INNER JOIN @matches As FT
ON T.Id = FT.[Key]
WHERE
(T.ESTADO IS NULL OR T.ESTADO = ' ')
ORDER BY
FT.RANK,
T.Pagina,
T.Parrafo,
T.Linea,
T.Palabra
; NB: Depending on your data, you may need to replace the table variables with temp tables.
NB2: Your table name is horrible! As you've noticed, you have to wrap it in square brackets every time you reference it, because you've included characters which are not valid in a SQL identifier. If at all possible, you should look to rename it using only A-Z, 0-9, and underscores.
Submit an article or tip