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
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;)
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.
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.
