Invalid length parameter passed to LEFT or SUBSTRING function. ERROR msg
I am working on a job request. I am trying to pull the SECOND WORD from the name of a stop, separated by spaces.
So, if the stop name is "Jane Doe", I want to pull out "Doe".
I have a field that I am trying to define in sql and it looks like this:
[pull the second word] = SUBSTRING([STOP_NAME], (CHARINDEX(' ', [STOP_NAME], 1)) + 1, (CHARINDEX(' ', [STOP_NAME], CHARINDEX(' ', [STOP_NAME], 1) + 1)) - (CHARINDEX(' ', [STOP_NAME], 1)) - 1)
When I enter this, I get the following error message:
Invalid length parameter passed to the LEFT or SUBSTRING function.
What am I doing wrong?
UPDATED:
I am trying to pull out only the second word. If the name of the stop was "Caroline Jane Doe" then I only want to pull out "Jane".
THANKS!
source to share
Why not just go with something like this? Take the LEN of the line, subtract the original CHARINDEX position of the space, and insert it at the start of the SUBSTRING:
[pull the second word] =
SUBSTRING([STOP_NAME],(CHARINDEX(' ',[STOP_NAME], 1)) + 1, LEN([STOP_NAME]) - CHARINDEX(' ',[STOP_NAME], 1))
source to share
This is because the length parameter (third) in the expression evaluates to a negative value. Your expression works if there are at least 2 spaces in the word (example "abc def ghi"). If there is only one space ("abc def") underneath the expression part, 0 is expressed, this can make the length (3rd parameter) negative and hence an error.
CHARINDEX ('', [STOP_NAME], CHARINDEX ('', [STOP_NAME], 1) + 1))
source to share