Click here to Skip to main content
15,071,519 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
When SELECT is used, the words to search are indicated. In the return, a set of records are obtained that have been matched with any of those words. For each record, I need to know what words have matched. It cannot be that records are returned without knowing the reasons, which are the matching words. How do i do it?

If I am doing Select of more than a hundred words and in each record I want to know which words have matched, now I have to go through the text of each record a hundred times to find if any word matches. Nobody cares?

Example: SELECT * FROM TABLE WHERE NAME = "A" OR NAME = "B";

THE REAL SELECT:
USE [Esp Derecho, BOE]
GO

DECLARE @sql VARCHAR(max);
DECLARE @sql1 VARCHAR(max);

SET @sql1 = 'casa OR edificio OR residencia OR mansión';
SET @sql1 = REPLACE(@sql1, '\"', CHAR(34));

SET @sql = 'SELECT [Libro_BOE - Código de Derecho Agroalimentario (Contexto Sectorial de la Industria Agroalimentaria)].*, FT.* FROM [Libro_BOE - Código de Derecho Agroalimentario (Contexto Sectorial de la Industria Agroalimentaria)] INNER JOIN CONTAINSTABLE([Libro_BOE - Código de Derecho Agroalimentario (Contexto Sectorial de la Industria Agroalimentaria)], [Contenido], ''' + @sql1 + ''') FT ON [Libro_BOE - Código de Derecho Agroalimentario (Contexto Sectorial de la Industria Agroalimentaria)].Id=FT.[Key] WHERE ([Libro_BOE - Código de Derecho Agroalimentario (Contexto Sectorial de la Industria Agroalimentaria)].ESTADO IS NULL OR [Libro_BOE - Código de Derecho Agroalimentario (Contexto Sectorial de la Industria Agroalimentaria)].ESTADO = '' '') ORDER BY RANK, Pagina, Parrafo, Linea, Palabra'; SET @sql = REPLACE(@sql, '\"', CHAR(34));

Print (@sql)
EXEC (@sql)

What I have tried:

in all internet. in all internet.
Posted
Updated 13-Sep-21 3:36am
v4
Comments
Wendelius 8-Sep-21 10:03am
   
Can you provide an example of source data and desired output
Member 14890678 8-Sep-21 10:18am
   
SELECT * FROM TABLE WHERE NAME = "A" OR NAME = "B";
Wendelius 8-Sep-21 10:20am
   
Okay, that's the select. What about the example data, how would the source data and desired output look like?
Member 14890678 8-Sep-21 10:32am
   
i have updated whit the real select generated by a function c#
Wendelius 8-Sep-21 10:39am
   
If I understand your question correctly, you're asking help with the select statement. In order to do that we'd need some example of the source data, how does it look like and what kind of output do you require, again some example data.

Without the examples it's hard to understand the question and to provide some kind of suggestions for the select statement.
Richard Deeming 8-Sep-21 10:05am
   
You have provided no code, no database schema, no sample data, and no expected output. Nobody can help you based on the information provided.
Member 14890678 8-Sep-21 10:18am
   
SELECT * FROM TABLE WHERE NAME = "A" OR NAME = "B";
Richard Deeming 8-Sep-21 10:26am
   
If that's genuinely the query, then you find out which word matched by looking at the Name column in the returned data.

But since that would have been trivial for you to work out, I can only assume you've provided a deliberately over-simplified version of your query.

We can't help you if you don't provide a reasonable description of the problem!
Member 14890678 8-Sep-21 10:31am
   
i have updated whit the real select generated by a function c#

It depends on your where clause.

For example this would be one way to approach it:

SQL
SELECT id. field1, CASE WHEN field2 LIKE 'test%' THEN 'test' ELSE 'apple' END AS matched_on
FROM table
WHERE (field2 LIKE 'test%' OR field2 LIKE 'apple%')


But without more details we can't help much.
   
Comments
Member 14890678 8-Sep-21 22:44pm
   
I need RANK so I use contains. In any case I need another "Constains" that is not limited to 4000 characters with Rank.
You can't do that with full-text search. The CONTAINSTABLE function only returns the key of the matching record and the rank. There is no way to see which word(s) matched.

Query with Full-Text Search - SQL Server | Microsoft Docs[^]

You will need to manually check for the matching words in the returned data. There are some suggestions here:
Hit-Highlighting in Full-Text Search - SQLPerformance.com[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900