Queries are limited to 4000 characters.

Question

Queries are limited to 4000 characters.

zequion 446

I am working on a semantic search engine and I need to be able to search for synonyms and inflections of the words in the phrase that the user enters.

I have encountered the problem that the parameter “contains_search_condition” of the Contains or ContainsTable command is limited to 4000 characters.

Just by searching for the synonyms and inflections of a word, this limit can be exceeded. It is not solved even if the words are entered into a Varchar(max) variable and passed to the parameter.

Several years ago I reported this problem and others that I have found in writing to Microsoft Spain, who reported it to Microsoft USA. They told me that "they were going to work on these problems."

I have not heard anything again and I need a solution, because I am stuck on this problem.

I can't write the text of the query because it says "violation of the code of conduct" and deletes the post.

Rodger Kong 365 Reputation points

2024-09-21T04:33:18.48+00:00

I don't think it has solution except MS changes the limitation or you guys can simpfy the search conditions.

Rodger Kong 365

I read your contains_search_condition, it's contains a shorter OR(|) string I name it A, a much longer OR string I name it B.

Now your condition is A&B.
Base on Boolean Algebra, you can split B to 2 part from a OR operation at the middle of string, because all operation is OR, it can be split any where. Named these 2 parts as C and D, now B = C | D.
So your condition will be A & (C | D)
From Boolean Algebra, A & (C | D) = (A & C) | (A & D)
You can treat UNION as OR, then you can write it as CONTAINSTABLE(A & C) UNION CONTAINSTABLE(A & D)

The SQL will be writen like below

--A&B
--A = word1|word11
--B = word2|word22|word3|word33|word4|word44|word5|word55
SELECT 
	T.id, T.f1, T.f2, FT.*
FROM 
	table1 T INNER JOIN
	CONTAINSTABLE(table1, f1, '(word1|word11) AND (word2|word22|word3|word33|word4|word44|word5|word55)') FT --A&(B|C)
	ON T.id = FT.[Key]

--The SQL above is equivalent to the below
--Splite B to C|D
--A&(C|D) = (A&C)|(A&D)
--A = word1|word11
--C = word2|word22|word3|word33
--D = word4|word44|word5|word55
SELECT 
	T.id, T.f1, T.f2, FT.*
FROM 
	table1 T INNER JOIN
	(
		SELECT * FROM CONTAINSTABLE(table1, f1, '(word1|word11)&(word2|word22|word3|word33)') --(A&C)
		UNION                                                                                 -- Operation OR
		SELECT * FROM CONTAINSTABLE(table1, f1, '(word1|word11)&(word4|word44|word5|word55)') --(A&D)
	)FT
	ON T.id = FT.[Key]

zequion 446 Reputation points

2024-09-22T00:39:44.51+00:00

Thank you for your help. I have made a SQL query that exceeds 4000 characters.

The user enters the text:

"annulment of a contract by a judgment due to dissolution or termination" -> (annulment of a contract by a judgment due to dissolution or termination).

Then, the program retrieves the synonyms of the relevant words. In this case, the user has selected that the search should find a synonym for each of the words entered.

Ex.:

W1_Synonym1|W1_Synonym2

AND

W2_Synonym1|W2_Synonym2

AND

W3_Synonym1|W3_Synonym2

If you can, make an example of what SQL code would have to be generated so that the query can be made even if the size is greater than 4000.
zequion 446 Reputation points

2024-09-22T00:51:16.2533333+00:00

I have tried the first SQL code that you have indicated and it has not worked.

3 answers

Your answer

Rodger Kong 365 Reputation points

2024-09-21T04:33:18.48+00:00

I don't think it has solution except MS changes the limitation or you guys can simpfy the search conditions.
Rodger Kong 365 Reputation points

2024-09-21T12:02:01.93+00:00

I read your contains_search_condition, it's contains a shorter OR(|) string I name it A, a much longer OR string I name it B.

Now your condition is A&B.

Base on Boolean Algebra, you can split B to 2 part from a OR operation at the middle of string, because all operation is OR, it can be split any where. Named these 2 parts as C and D, now B = C | D.

So your condition will be A & (C | D)

From Boolean Algebra, A & (C | D) = (A & C) | (A & D)

You can treat UNION as OR, then you can write it as CONTAINSTABLE(A & C) UNION CONTAINSTABLE(A & D)

The SQL will be writen like below

--A&B --A = word1|word11 --B = word2|word22|word3|word33|word4|word44|word5|word55 SELECT T.id, T.f1, T.f2, FT.* FROM table1 T INNER JOIN CONTAINSTABLE(table1, f1, '(word1|word11) AND (word2|word22|word3|word33|word4|word44|word5|word55)') FT --A&(B|C) ON T.id = FT.[Key] --The SQL above is equivalent to the below --Splite B to C|D --A&(C|D) = (A&C)|(A&D) --A = word1|word11 --C = word2|word22|word3|word33 --D = word4|word44|word5|word55 SELECT T.id, T.f1, T.f2, FT.* FROM table1 T INNER JOIN ( SELECT * FROM CONTAINSTABLE(table1, f1, '(word1|word11)&(word2|word22|word3|word33)') --(A&C) UNION -- Operation OR SELECT * FROM CONTAINSTABLE(table1, f1, '(word1|word11)&(word4|word44|word5|word55)') --(A&D) )FT ON T.id = FT.[Key]
zequion 446 Reputation points

2024-09-22T00:39:44.51+00:00

Thank you for your help. I have made a SQL query that exceeds 4000 characters.

The user enters the text:

"annulment of a contract by a judgment due to dissolution or termination" -> (annulment of a contract by a judgment due to dissolution or termination).

Then, the program retrieves the synonyms of the relevant words. In this case, the user has selected that the search should find a synonym for each of the words entered.

Ex.:

W1_Synonym1|W1_Synonym2

AND

W2_Synonym1|W2_Synonym2

AND

W3_Synonym1|W3_Synonym2

If you can, make an example of what SQL code would have to be generated so that the query can be made even if the size is greater than 4000.
zequion 446 Reputation points

2024-09-22T00:51:16.2533333+00:00

I have tried the first SQL code that you have indicated and it has not worked.

Answer 1

LiHongMSFT-4306 31,576

Hi @zequion

Have you tried split the search conditions into multiple smaller conditions that fit with the 4000-character limit?

Best regards,

Cosmog

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

zequion 446 Reputation points

2024-09-19T22:29:10.4933333+00:00

The query cannot be split because it is conditional on all the words that make it up. All the words must be involved at the same time.
zequion 446 Reputation points

2024-09-22T06:06:19.5333333+00:00

That's what I'm thinking, but then instead of 1 Select I have 1 for each word the user enters and each Select is limited to 4000. This is all because Microsoft doesn't increase the size of Select to unlimited by changing the type of variable it uses. It's a limitation that comes from the 80s.

Answer 2

zequion 446

Can you contact someone who can fix this? I've already tried and they've been saying for years that "they'll fix it".

Viorel 122.8K Reputation points

2024-09-20T04:44:13.1766667+00:00

Maybe it can be split into several smaller SQL statements, and the results can be combined using UNION and INTERSECT.

Answer 3

Our purpose is shrink the length of param, the only way is split it to multiple parts. The result of CONTAINSTABLE is a rowset like table, so it can JOIN with standard table. So it can runs UNION. That the reason we can resolve your requirement. We can split the condition to parts and execute them with CONTAINSTABLE mutiple times, at last stick each of results together by UNION.

I split your code, cut line 2nd and 4th to 2 parts, because them looks longer.

I treated line 1st 3rd, 5th as one piece, because they are all connected by AND. It can be named A.

Line 2nd was split to 2 parts, named B and C, so line 2nd = B | C.

So did line 4th , named D and E, line 4th = D | E.

Now, total condition string is simpfied to A & (B | C) & (D| E).

Expanding the expression, it will be (A & B & D) | (A & B & E) | (A & C & D) | (A & C & E).

UNION can be treated as OR, replace it in the expression, can get

(A & B & D)

UNION

(A & B & E)

UNION

(A & C & D)

UNION

(A & C & E)

Use this to process your condition, get the code as in the attachment, and it should be look like this

SQL Split

zequion 446 Reputation points

2024-10-10T03:58:26.8133333+00:00

Can you delete this comment? I've found that it doesn't work.

Share via

Queries are limited to 4000 characters.

3 answers

Your answer