Home/ Questions/How can I handle -invalid length parameter passed to the left or substring function?
Next
Answered
Aaron Jacquot
  • 10
Asked: 2022-05-12T11:50:22+00:00 In:

How can I handle -invalid length parameter passed to the left or substring function?

  • 10

. Advertisement .

..3..

. Advertisement .

..4..

I am working on programs, but I found the following warning message:

Invalid length parameter passed to the LEFT or SUBSTRING function

Is there any way to stabilize the issue “invalid length parameter passed to the left or substring function”? I read a lot of topics about this, but all of them were trying to install anything. Is this the correct way, or any recommendation for me? Please find the beginning command below:

SUBSTRING(PostCode, 1 , CHARINDEX(' ', PostCode ) -1)
invalid length parameter
Share

3 Answers

  1. Best Answer
    Expert

    The cause: This error only occurs if PostCode is missing any spaces.

    Solution: This could be done by adding conditionality so that PostCode can still be retrieved if a space is not found.

    select SUBSTRING(PostCode, 1 , 
case when CHARINDEX(' ', PostCode ) = 0 then LEN(PostCode) 
else CHARINDEX(' ', PostCode) -1 end)
    • 9

  2. CHARINDEX will return 0, if there are no spaces in the string. If not, you can search for -1 substrings.

    To avoid this, you can add a trailing space to the string at the end to ensure that there is always at most one space. 

    SELECT SUBSTRING(PostCode, 1 , CHARINDEX(' ', PostCode + ' ' ) -1)
    • 14

Leave an answer

You must login to add an answer.

Need An Account,