Know the words that have matched in the return of select

Question

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 8-Sep-21 3:57am

Member 14890678

Updated 13-Sep-21 3:36am

v4

Add a Solution

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#

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Member 15329613 · Answer 1 · 2021-09-08T04:11:00

Solution 1

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.

Posted 8-Sep-21 4:11am

Member 15329613

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.

Richard Deeming · Answer 2 · 2021-09-08T04:38:00

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[^]