question

zequion-6374 avatar image
0 Votes"
zequion-6374 asked zequion-6374 published

Know the Words that have Matched in the Return of Select

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 of SELECT generated by the c# function seen in SSMS:
Although only OR words appear in the example, there may be AND or word with asterisk or (). They are not just a list of OR Words, it is what can be assigned to the contains_search_condition parameter of CONTAINSTABLE.
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)


Result:
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], 'casa OR edificio OR residencia OR mansión') 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

Mens. 103, Nivel 15, Estado 1, Línea 3
The string that begins with 'casa OR edificio OR residencia' is too long. The maximum length is 4000.

Even though I put the Sql string in a Varchar (max) variable, the Error still appears. If I trim the chain to 4000 it works fine.

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered zequion-6374 commented

Your question could be clearer - it would certainly have helped if you had included your query. It sounds like you are using LIKE or CONTAINS.

One approach is run separate queries for each word and combine them with UNION and concatenate them with string_agg at the end:

; WITH u AS (
    SELECT @word1 AS word, keycol FROM tbl WHERE CONTAINS(mycol, @word1
    UNION ALL
    SELECT @word2, keycol FROM tbl WHERE CONTAINS(mycol, @word2
    ...
)
SELECT keycol, string_agg(word, ' ')
FROM  u

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

They are not just a list of OR Words, it is what can be assigned to the contains_search_condition parameter of CONTAINSTABLE.

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered zequion-6374 commented

Hi @zequion-6374,

Welcome to Microsoft Q&A!

We recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

Per my understanding, you could consider to store the hundreds of words in one table and proceed with join two tables.

If each of your record is one sentence with multiple blanks, you could refer below simple example.

 create table #table1
 (record varchar(1000))
    
 insert into #table1 values
 ('We don’t see things as they are, we see them as we are.'),
 ('Remember that failure is an event, not a person.'),
 ('If you wait, all that happens is you get older.')
    
 create table #table2
 (word varchar(100))
    
 insert into #table2 values
 ('we'),('you'),('If'),('me'),('all'),('that')
    
 select distinct a.record,b.word from (
 select * from #table1
   CROSS APPLY STRING_SPLIT(record, ' '))a
   inner join #table2 b on a.value=b.word

Output:

 record    word
 If you wait, all that happens is you get older.    all
 If you wait, all that happens is you get older.    If
 If you wait, all that happens is you get older.    that
 Remember that failure is an event, not a person.    that
 We don’t see things as they are, we see them as we are.    we
 If you wait, all that happens is you get older.    you

If there is no blank or other special character, you could refer another example from below:

 create table #table3
 (record varchar(1000))
    
 insert into #table3 values
 ('Wedon’tseethingsastheyare,weseethemasweare.'),
 ('Rememberthatfailureisanevent,notaperson.'),
 ('Ifyouwait,allthathappensisyougetolder.')
    
 create table #table4
 (word varchar(100))
    
 insert into #table4 values
 ('we'),('you'),('If'),('me'),('all'),('that')
    
 select * from #table3 a,#table4 b where CHARINDEX(b.word,a.record)>0

Output:

 record    word
 Wedon’tseethingsastheyare,weseethemasweare.    we
 Rememberthatfailureisanevent,notaperson.    me
 Rememberthatfailureisanevent,notaperson.    that
 Ifyouwait,allthathappensisyougetolder.    you
 Ifyouwait,allthathappensisyougetolder.    If
 Ifyouwait,allthathappensisyougetolder.    all
 Ifyouwait,allthathappensisyougetolder.    that

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

They are not just a list of OR Words, it is what can be assigned to the contains_search_condition parameter of CONTAINSTABLE.

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @zequion-6374,

It seems that you posted duplicated posts.
Quick Way to Divide a Select into Multiple Selects (Great Limitation of CONTAINS)
Sql String Greater than 4000 characters

As mentioned by other experts, CONTAINSTABLE is not allowed to accept such long search conditions. Maximum length is 4000. Even if you changed to varchar(max).

Right now you have to trim it or split it into multiple select statements to make sure the length of your variable is within 4000.

Thank you for understanding!

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.