Click here to Skip to main content
15,078,243 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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 SH*TS because they say that if they do, it can affect the users' code already created.

I am using OR / AND concatenated words that are part of the contains_search_condition parameter of Containstable and the problem is that it is limited to 4000 characters. Although only OR words appear in the example, there may be AND or word with asterisk or ().

Example of SELECT generated by the c# function seen in SSMS:

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

What I have tried:

in all internet. in all internet.
Posted
Updated 13-Sep-21 3:35am
v4
Comments
CHill60 8-Sep-21 3:49am
   
If that was me I would push those values into an actual table
Member 14890678 8-Sep-21 9:51am
   
I would like to understand what you say. Do you have an example? Why do you think the problem would be solved?
CHill60 8-Sep-21 10:04am
   
I was thinking that a table of the stuff you are doing the full text search on would be more flexible that creating a huge sql statement. I don't do this myself (full text search that is) but I did find these posts that were trying to do something similar
sql server - Full text search using words stored in another table - Database Administrators Stack Exchange[^]
sys.dm_fts_index_keywords_by_document (Transact-SQL) - SQL Server | Microsoft Docs[^]
Member 14890678 8-Sep-21 10:41am
   
I am using OR AND AND concatenated words that are part of the contains_search_condition parameter of Containstable and the problem is that it is limited to 4000 characters.
Member 14890678 8-Sep-21 10:53am
   
I don't use Full text because I know it and have found many bugs. They abandoned it as soon as it was created in 2008.
CHill60 8-Sep-21 11:52am
   
The confusion arose because you mentioned CONTAINS. Quote: "CONTAINS is a predicate used in the WHERE clause of a Transact-SQL SELECT statement to perform SQL Server full-text search on full-text indexed columns containing character-based data types." and "CONTAINSTABLE ... performs a SQL Server full-text search on full-text indexed columns containing character-based data types"
I don't who "They" are.
Perhaps if you explained exactly what you are trying to search for and what the table looks like it would help, although I see someone has now posted a solution

Jörgen Andersson 8-Sep-21 4:31am
   
I don't have a proper solution, nor time to create one.
But if you only need to save a few bytes you can exchange ' OR ' for a vertical bar '|'
Member 14890678 8-Sep-21 9:54am
   
It also hurts my view to see OR but what it says would save me 1 byte x words = 100 bytes
CHill60 8-Sep-21 10:05am
   
It would be 4 bytes per word search surely?
| OR |
|1234|

1 solution

Combining this with your other question[^], something like this should work - although it probably won't be particularly quick:
SQL
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, /* TODO: Use the correct type for your table's primary key */
    [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.
   
Comments
Member 14890678 8-Sep-21 12:09pm
   
Your response is impressive and I appreciate it. I want to clarify that I use CONTAINS / CONTAINSTABLE because I am also interested in the RANK value and for that reason it is not possible for me to use SELECT Like, because it does not have RANK. Although in the example all the words are OR, depending on the fields on the screen, it is also possible to generate AND words or words with an asterisk * word * or groupings of words with parentheses (). It is all very interesting but it is not just about retrieving a list of words from a table. Can the table replace the logic of OR and AND? Can't you just put that part of the Sql query in the table and include it in Containstable's contains_search_condition parameter? Also, keep in mind that I generate the query from a c # function, not from SSMS and I execute it with SqlCommand.ExecuteReader (). It is about bypassing the 4000 character restriction without losing the logic of the dynamic Sql query. For now I am trying to understand your code better.
Richard Deeming 9-Sep-21 4:18am
   
Trying to parse a more complicated query in T-SQL is a non-starter. The language is designed for querying, not for building a generic grammar parser.

I think you're going to need to try to split up your query into smaller chunks in your C# code. For example, (a AND b) OR (c AND d) can be split into two separate queries - a AND b, and c AND d. However, (a OR b) AND (c OR d) would need four queries: a AND c, a AND d, b AND c, and b AND d.

If you can split it up into small enough chunks in your C# code, you can pass the chunks through as a table-valued parameter to replace the @words table in my solution.
Using SQL Server's Table Valued Parameters - Brent Ozar Unlimited®[^]

Obviously the "matched words" column would then contain the matching query fragments instead of the individual words.
Member 14890678 9-Sep-21 9:01am
   
That's a technical nightmare. I can spend 2 months working on that when it would be very easy for Microsoft to change the type of variable so that the queries were unlimited. The problem is that they don't fix anything because they say it would affect users' already created code. So if 30 years ago they screwed up, we have to spend our entire lives bumping into those stones. So, the problem is not that there are errors, but that they are NEVER fixed. I want to present you my website so that you can see the program. It's called Books Explorer.
Richard Deeming 9-Sep-21 9:16am
   
"... when it would be very easy for Microsoft to change the type of variable so that the queries were unlimited"

Even if that were true - and remember, we don't know any of the details of how CONTAINS is implemented, nor what impact the change might have on other code - it wouldn't happen as part of a cumulative update. You'd have to upgrade to a new version of SQL Server.

Quite apart from the cost involved, assessing the impact that might have on the rest of your code and databases would probably take more than two months.

"I want to present you my website ..."
Please don't. Unsolicited links to websites or YouTube videos is spam, and will get you kicked off the site.

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