Get string between 3rd and 5th space

Question

I am trying to extract the string between the 3rd and 5th space of a value. For instance, if the string is:

1588 Exeter Rd, Lebanon, CT 06249, United States

What I want to extract is:

Lebanon, CT

I've been messing around with some of the solutions I found online but it looks like Im missing something. For instance, for the following query:

SUBSTRING([@field:GPS], (CHARINDEX(' ', [@field:GPS], 
CHARINDEX(' ', [@field:GPS], 
CHARINDEX(' ',[@field:GPS])
+1 )+1)+1), 


CHARINDEX(' ', [@field:GPS], 
CHARINDEX(' ', [@field:GPS], 
CHARINDEX(' ', [@field:GPS], 
CHARINDEX(' ', [@field:GPS], 
CHARINDEX(' ',[@field:GPS])+1 )+1 )+1 )+1)+1)

it returns this: Lebanon, CT 06249, United Sta

Answer 1

The query is almost correct, but seems to be extracting too much of the string. You need to calculate the length of the substring you want to extract, rather than just finding the position of the 5th space.

Here's a modified query that might work:

SUBSTRING([@field:GPS], 
  CHARINDEX(' ', [@field:GPS], CHARINDEX(' ', [@field:GPS], CHARINDEX(' ', [@field:GPS]) + 1) + 1) + 1,
  CHARINDEX(' ', [@field:GPS], CHARINDEX(' ', [@field:GPS], CHARINDEX(' ', [@field:GPS], CHARINDEX(' ', [@field:GPS], CHARINDEX(' ', [@field:GPS]) + 1) + 1) + 1) + 1) -
  CHARINDEX(' ', [@field:GPS], CHARINDEX(' ', [@field:GPS], CHARINDEX(' ', [@field:GPS]) + 1) + 1) - 1
)

This query calculates the length of the substring by subtracting the position of the 3rd space from the position of the 5th space, and then subtracting one more to exclude the space itself.