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.
Invalid length parameter passed to the RIGHT function
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
2 additional answers
Sort by: Most helpful
-
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.
-
LiHongMSFT-4306 31,566 Reputation points
2023-06-19T06:54:06.36+00:00 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.