Get a string between the 2nd and 3rd instances of a known string

Given the string 'aaaaa$bbbbb$ccccc$ddddd', I need to get the value between the 2nd and third $. I.e. ccccc.

There was an SO question that gave me a good starting point, but I'm not quite there. (So sorry, I had the page for the question, did some more searching and then lost it in my history. Otherwise I would've included a link to that question.)

The OP in that question needed to get bbbbb from aaaa$bbbbb$ccccc. The answer was this code:

SELECT SUBSTRING('aaaaa$bbbbb$ccccc', CHARINDEX('$', 'aaaaa$bbbbb$ccccc') + 1, CHARINDEX('$', 'aaaaa$bbbbb$ccccc', CHARINDEX('$', 'aaaaa$bbbbb$ccccc') + 1) - CHARINDEX('$', 'aaaaa$bbbbb$ccccc') - 1) as My_String

I worked it out to this current code:

SUBSTRING('aaaaa$bbbbb$ccccc$ddddd', CHARINDEX('$', 'aaaaa$bbbbb$ccccc$ddddd') + 1, CHARINDEX('$', 'aaaaa$bbbbb$ccccc$ddddd', CHARINDEX('$', 'aaaaa$bbbbb$ccccc$ddddd') + 1 + CHARINDEX('$', 'aaaaa$bbbbb$ccccc$ddddd') + 1) - CHARINDEX('$', 'aaaaa$bbbbb$ccccc$ddddd') - 1) as My_String

Which is returning bbbbb$ccccc

So I figure I don't have either the second CHARINDEX() or the CHARINDEX() in the subtraction part of the SUBSTRING() corret.

Solution

This is much simpler if you treat it as a string split:

SELECT value
  FROM STRING_SPLIT(@string,'$',1)
 WHERE ordinal = 3

If you must do it as string manipulation you can use:

SELECT @string, 
CHARINDEX('$',@string, CHARINDEX('$',@string)+1) AS SecondOccurrence Position,
CHARINDEX('$',@string,CHARINDEX('$',@string, CHARINDEX('$',@string)+1)+1) AS ThirdOccurrence Position,
SUBSTRING(@string,CHARINDEX('$',@string, CHARINDEX('$',@string)+1)+1,CHARINDEX('$',@string,CHARINDEX('$',@string, CHARINDEX('$',@string)+1)+1)-CHARINDEX('$',@string, CHARINDEX('$',@string)+1)-1)

What we're doing here is finding the second occurrence, using CHARINDEX with the optional third parameter of Start Position, and providing the position of the first occurrence. Then we do the same thing again, with the composite CHARINDEX providing the position of the second occurrence as the start position. Now we know both positions, we can use them with SUBSTRING to provide a start position (first occurrence position +1) and a length (third occurrence - second occurrence).