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

    IT Manager – Public Sector

    “Xynomix provide us with remote monitoring and administration for Linux, as well as Oracle. The proactive monitoring aspect of our service has proved extremely valuable. Whenever potential issues have been recognised with the system, it has always been identified by the monitoring technology and Xynomix DBAs begin work on a preventative fix immediately.”

    Read More »
    Testimonials

    Network Administrator – Dragon LNG Ltd

    “We recently installed a new application which ran on Oracle but was unsure how to correctly license the environment. As a result, we engaged with Xynomix to advise on how best to do so. Xynomix ran an Independent Oracle License Review, which gave us full visibility on our license compliance. They highlighted the restrictions around various license types, whilst also detailing how to avoid any pitfalls moving forward.”

    Read More »