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.

Speak to a Xynomix DBA

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

Group IT Manager – Manufacturing Industry

“Being able to trust a supplier is the goal of any relationship, and that’s exactly what we’ve achieved with Xynomix.”

Testimonials

Lead Database Administrator – Retail Sector

“My team are always flat out ensuring that the Oracle database system is up and running without any problems. Sometimes when there is a larger issue we need a helping hand. Xynomix give us that option.”

Testimonials

Data Systems Manager – Media Industry

“Xynomix investigated the performance bottlenecks that we were experiencing after updating our data warehouse application. After investigating the system, Xynomix concluded that the database server’s cache-hit rates and available memory were both low, which was impacting negatively on performance. The technician rectified this and we are now very happy with the function of the application.”