Invalid length parameter passed to the RIGHT function

Question

Hello, I have created SSRS report with following query. It was working till last week and suddenly giving error "Msg 536, Level 16, State 4, Line 1

Invalid length parameter passed to the RIGHT function."

SQL query

select right(SoftwareCode0, charindex(';', reverse(SoftwareCode0)) - 1) [Code],

right(PackageType0, charindex(':', reverse(PackageType0)) - 1) [Package],

right(ProductCode0, charindex(';', reverse(ProductCode0)) - 1) [Product],

right(ProgramName0, charindex(';', reverse(ProgramName0)) - 1) [Program],

right(PackageID0, charindex(';', reverse(PackageID0)) - 1) [PackageID],

right(ProgramVersion0, charindex(';', reverse(ProgramVersion0)) - 1) [Version],

DateOfCreation0 [Date]

from Applications64 where KeyName0 = 'app_64'

Am using SQL 2017 CU31 x64 Standard. Please suggest.

Answer 1

Probably one of the new values is empty or does not contain “;”. Try some more complex expressions, such as select case charindex(';', reverse(SoftwareCode0)) when 0 then SoftwareCode0 else right(SoftwareCode0, charindex(';', reverse(SoftwareCode0)) - 1) end [Code], etc, or define a helper function that simplifies this.

Answer 2

Invalid length parameter passed to the RIGHT function."

Happens when here

select right(SoftwareCode0, charindex(';', reverse(SoftwareCode0)) - 1) [Code],

CHARINDEX return 0 , -1 = -1 and -1 is an invalid parameter value for RIGHT function.

Answer 3

Hi @Mayursingh Rajput

Msg 536, Level 16, State 4, Line 1 Invalid length parameter passed to the RIGHT function.

As experts explained, the length parameter cannot be negative number.

If the purpose of this query is to remove ; Or : then you can try the Replace function.

Like this: Replace(SoftwareCode0,';','') [Code]

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.