How to extract specific text between the second and the third "_" with CHARINDEX and SUBSTRING?

Question

How to write SQL script to get "Cabforce" out of "Email_Transport_Cabforce_NEB_Fallback_LB"?

I found solution below to get all words between first _ and last _ but failed to transform it to get a string between the second _ and the third _.

DECLARE @c varchar(100)
SET     @c = 'Email_Transport_Cabforce_NEB_Fallback_LB' 

SELECT SUBSTRING(
    @c, 
    CHARINDEX('_', @c) + 1, 
    LEN(@c) - CHARINDEX('_', @c) - CHARINDEX('_', REVERSE(@c))
)

Answer 1

This shows how you can build it: I've broken it down into pieces; you can see the logic and put it all back into one expression:

DECLARE @c varchar(100) SET @c = 'Email_Transport_Cabforce_NEB_Fallback_LB'

DECLARE @firstIndex int = CHARINDEX('_', @c) + 1
DECLARE @secondIndex int = CHARINDEX('_', @c, @firstIndex) + 1
DECLARE @thirdIndex int = CHARINDEX('_', @c, @secondIndex) + 1

SELECT SUBSTRING( @c, @secondIndex, @thirdIndex - @secondIndex - 1)