This section covers the specifics of executing the SUBSTRING() function of the query language in the client/server mode and the tips for building queries with this function.
SUBSTRING() function
In the 1C:Enterprise query language the SUBSTRING() function has the following format: SUBSTRING(<Source string>, <Start point>, <Length>). It is applied to string data. It selects a part of the <Source string> that begins with the character at <Start point> position (the first character is considered to have number 1) and has a length of <Length> characters. The result of the SUBSTRING() function is a string of variable length and its length is considered unlimited if <Source string> has unlimited length and the <Length> parameter is not a constant or is greater than 1024.
Calculation of the SUBSTRING() function on an SQL Server
In the client/server mode, the SUBSTRING() function is implemented using the SUBSTRING() function of the respective SQL operator passed to the database server (SQL Server). The server calculates the result type of the SUBSTRING() function using sophisticated rules taking into account the type and values of its parameters as well as the context it is used in.
In most cases, these rules do not impact the execution of the 1C:Enterprise query, but sometimes the maximum length of the resulting string calculated by the SQL Server is important for query execution. Note that in some SUBSTRING() function usage scenarios the maximum length of its result can be equal to the maximum number of the limited-length string, which is 4000 characters in SQL Server. This may result in unexpected termination of query execution.
For example, the following query:
SELECT CASE WHEN Type = &LegalAddressOfIndividual THEN SUBSTRING(Presentation, 0, 200) ELSE NULL END AS Presentation, CASE WHEN Type = &LegalAddressOfIndividual THEN SUBSTRING(Presentation, 0, 200) ELSE NULL END AS Presentation1 FROM InformationRegister.ContactInfo AS ContactInfo ORDER BY Presentation, Presentation1
is terminated with the following message:
DBMS error:
Microsoft OLE DB Provider for SQL Server: Warning: The query processor could not produce a query plan from the optimizer because the total length of all the columns in the GROUP BY or ORDER BY clause exceeds 8000 bytes.
HRESULT=80040E14, SQLSTATE=42000, native=8618
This is due to the fact that the maximum string length is calculated by Microsoft SQL Server using the following expression:
CASE WHEN Type = &LegalAddressOfIndividual THEN SUBSTRING(Presentation, 0, 200) ELSE NULL END AS Presentation
equals 4000 characters. This is why the length of a record that contains two such fields is more than 8000 bytes allowed to execute the sorting operation.
Due to this behavior of the SUBSTRING() function on the SQL Server, we recommend that you do not use the SUBSTRING() function to convert unlimited length strings to limited length strings. We recommend that you use CAST() instead. So the above example can be rewritten as follows:
SELECT CASE WHEN Type = &LegalAddressOfIndividual THEN CAST(Presentation AS String(200)) ELSE NULL END AS Presentation, CASE WHEN Type = &LegalAddressOfIndividual THEN CAST(Presentation AS String(200)) ELSE NULL END AS Presentation1 FROM InformationRegister.ContactInfo AS ContactInfo ORDER BY Presentation, Presentation1Next page: Specifics of sorting by reference fields