Invalid length parameter passed to the RIGHT function

Mayursingh Rajput 41 Reputation points
2023-06-19T04:29:06.4133333+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,492 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 122.3K Reputation points
    2023-06-19T05:22:44.97+00:00

    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.

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Olaf Helper 47,416 Reputation points
    2023-06-19T06:16:23.58+00:00

    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.

    0 comments No comments

  2. LiHongMSFT-4306 31,566 Reputation points
    2023-06-19T06:54:06.36+00:00

    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.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.