Specifics of executing SUBSTRING() function of the query language

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,
    Presentation1
Next page: Specifics of sorting by reference fields

Be the first to know tips & tricks on business application development!

A confirmation e-mail has been sent to the e-mail address you provided .

Click the link in the e-mail to confirm and activate the subscription.