question

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

Quick Way to Divide a Select into Multiple Selects (Great Limitation of CONTAINS)

I use c# and Sql Server to do a SELECT query for synonyms and inflections of various words. When using the CONTAINS or CONTAINSTABLE command, the parameter "<contains_search_condition>" is limited to 4000 characters. Only synonyms and inflections of a single word can exceed that limit. The SELECT of the query is generated by a c# function and the generated result depends on various fields on the screen. I find it very complicated to add a code that divides the current SELECT into multiple SELECTs. I need a quick way to divide an already generated SELECT into multiple SELECTs.

Microsoft would find it very easy to modify the "<contains_search_condition>" parameter to be of type Varchar (Max), but Microsoft NEVER SOLVES THEIR S**** because they say that if they do, it can affect the users' code already created.

Example of SELECT generated by the c# function seen in SSMS:
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-generaldotnet-csharp
· 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.

Since there is a known limit, either change the data type as per the following post or refactor your C# code to perform multiple SELECT statements and either store results in a container locally or perhaps in a temp table and work results once all SQL has executed.

Microsoft would find it very easy to modify the "<contains_search_condition>" parameter . . .

Doubtful, consider the user base a change like this would affect, not going to happen anytime soon.


0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered zequion-6374 commented

SELECT query for synonyms and inflections of various words

What a ... code.
You know that SQL Server Full text search supports a Thesarus for inflection/synonyms for words; why not using that feature?



· 2
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.

I know Full-text search well. I have found serious bugs and errors that I have already reported to Microsoft. It is a system that has been abandoned since 2008. I have improved that system through my own programming but there are still Microsoft precariousnesses that harm me.

0 Votes 0 ·

I need to be able to use ContainsTable, because it uses Rank, and with the contains_search_condition parameter that supports more than 4000 characters.

0 Votes 0 ·
ArtemiyMorozUA avatar image
0 Votes"
ArtemiyMorozUA answered zequion-6374 commented

hi there! I would advise you to save all those words to a separate table and then use some JOIN logic.

· 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.

Can you make a practical example?

0 Votes 0 ·
Paul-5034 avatar image
0 Votes"
Paul-5034 answered

Where does your list of synonyms and inflections come from originally?

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.

Bruce-SqlWork avatar image
0 Votes"
Bruce-SqlWork answered OlafHelper-2800 commented

The issue is that the string concatenation function returns a string, not a varchar(max) which is a stream of bytes backed by a temp table store. This is not likely to change.

You may want to switch to sql extension language that could generate larger dynamic strings.

· 3
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.

How to switch to the SQL extension language which could generate larger dynamic strings. Do you know where the information is?

0 Votes 0 ·

what is sql extension language? I do not understand you. I don't know what that is. Helloooooo?

0 Votes 0 ·