Invalid length parameter passed to the substring function

SQL Server Error Message: Invalid length parameter passed to the substring function

Error Message:

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

Causes:

This error is caused by passing a negative value to the length parameter of the SUBSTRING, LEFT and RIGHT string functions.  This usually occurs in conjunction with the CHARINDEX function wherein the character being searched for in a string is not found and 1 is subtracted from the result of the CHARINDEX function. 

LEFT(@String, CHARINDEX(' ', @String) - 1)

If the character is not found in a string, a space in this example, the CHARINDEX function will return a value of 0.  Subtracting 1 to this will become -1 and using this as the length parameter in the SUBSTRING or LEFT functions will result to this error.

Need Additional Assistance?

Looking for a second opinion on a SQL Server error code? Get in touch with
Xynomix and we'll assess your issue and help to action a fix.

Contact Us


    On submitting this form, Xynomix will store your details and may contact you in relation to your request. For more information on how we process data, please see our privacy notice.

    Testimonials

    Testimonials

    ICT Group Manager – Southend-on-sea Borough Council

    “We chose Xynomix from the multiple providers approached for our support requirement for a number of reasons. Initially we were impressed with their responsiveness and deep technical knowledge of both Oracle and Microsoft SQL Server databases. Their flexibility in providing mentoring to our on-site team meant increased cost efficiency against the already competitive annual costs and consultancy rates. We’re fully confident in their understanding of our needs and see them as an extension of our own team.”

    Read More »
    Testimonials

    Business Services – Director

    “We do not have the resources to employ full time DBAs. Xynomix give us the confidence to go out and win new business, knowing that we can deliver our services and our systems can cope.”

    Read More »
    Testimonials

    IT Manager – Further Education

    “The optimisation, support and management of our Oracle database arrangement by Xynomix have increased the efficiency of the Oracle database and reduced the total running cost, whilst at the same time allowing us to provide a better service for staff and student end-users.”

    Read More »