3

I am working on a query for work. I am attempting to pull the SECOND WORD of a stop name, separated by spaces.

So if the stop name is "Jane Doe" I want to pull "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 type this in, 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 the second word only. If the stop name had been "Caroline Jane Doe", then I want to pull just "Jane."

THanks!

pynewbee
  • 553
  • 1
  • 6
  • 16

2 Answers2

2

This is because the length( 3rd) parameter in your expression evaluating 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'), below part of your expression evaluates to 0, This can make the length (3rd parameter) negative and so the error.

CHARINDEX(' ', [STOP_NAME], CHARINDEX(' ', [STOP_NAME], 1) + 1))

RKRC
  • 119
  • 1
  • 4
1

Why not just go with something like this? Take the LEN of the string, subtract the starting position of the CHARINDEX of the space, and plug that into the start position of the SUBSTRING:

[pull the second word] =
SUBSTRING([STOP_NAME],(CHARINDEX(' ',[STOP_NAME], 1)) + 1, LEN([STOP_NAME]) - CHARINDEX(' ',[STOP_NAME], 1))
Jacob H
  • 2,359
  • 1
  • 9
  • 28
  • thank you for the suggestion! this method definitely works if i only have two words like "Jane Doe." However, if I had multiple words such as "Caroline Jane Doe" and I wanted to pull the SECOND word "Jane", it will not work, as it pull the entire "jane doe". – pynewbee May 21 '17 at 19:02
  • You are correct, it does only return the data after the first space character. – Jacob H May 22 '17 at 12:31