Fix issue with "invalid length parameter passed to the left or substring function".

Question

Fix issue with "invalid length parameter passed to the left or substring function".

mo boy 396

Dear experts,

There is this code but it appears to fail with this message.

"invalid length parameter passed to the left or substring function". Please advise how to fix this?
This is not the whole code but this is where the issue lies as I don't see anywhere else in the code using substring function.

substring(emp_list.[Pay Department], 1, charindex('-', emp_list.[Pay Department]) - 2) AS 'Division Name',

CASE when (len(emp_list.[Pay Department]) - len(replace(emp_list.[Pay Department],'-',''))) < 2
THEN ''
ELSE substring((right(emp_list.[Pay Department],(len(emp_list.[Pay Department]) - (CHARINDEX('-', emp_list.[Pay Department])+1)))), 1, charindex('-', (right(emp_list.[Pay Department],(len(emp_list.[Pay Department]) - (CHARINDEX('-', emp_list.[Pay Department])+1))))) - 2)
END AS 'Unit Name'

Thanks

Yitzhak Khabinsky 27,091 Reputation points

2022-11-10T14:44:13.993+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
Jingyang Li 5,901 Reputation points Volunteer Moderator

2022-11-10T15:25:23.707+00:00

Your data caused the substring function you are using failed. You may need a different way to split your value. You need to provide sample data to represent your question and find a solution (your expected result). To get rid of your error in your code, you need to handle the case that will cause problem.

Answer accepted by question author

0 additional answers

Your answer

Yitzhak Khabinsky 27,091 Reputation points

2022-11-10T14:44:13.993+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
Jingyang Li 5,901 Reputation points Volunteer Moderator

2022-11-10T15:25:23.707+00:00

Your data caused the substring function you are using failed. You may need a different way to split your value. You need to provide sample data to represent your question and find a solution (your expected result). To get rid of your error in your code, you need to handle the case that will cause problem.

Answer 1

LiHongMSFT-4306 31,616

Hi @mo boy
At the first glance of this issue, this may cause by your source data.
Check this sample: select substring('abc',1,charindex('-','abc') - 2) which will receive same error message.
CHARINDEX will return 0 if no '-' are in the string and then you look for a substring of **0 - 2 = -2 ** length.

Therefore, you'd better check if there is any special data in the source data table that causes the function to return a negative number. You can split these functions such as charindex to check which one returns a negative value.

Best regards,
LiHong

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.

Satya 0 Reputation points

2023-11-30T11:44:42.63+00:00

I have a similar issue. but like you said I verified the char index to see any columns giving result 0, but I didn't get any.

and if I remove -1 from charindex in my whole substring and that time I am getting results.

how to find the junk data?
Naomi Nosonovsky 8,881 Reputation points

2023-11-30T21:39:09.5833333+00:00

What is your expression? May be your charindex returns 1 and then you're passing 0 to the function.

Share via

Fix issue with "invalid length parameter passed to the left or substring function".

0 additional answers

Your answer