TSQL: How to use charindex ? – Finding a specific word in a variable string between specific characters

I have this string: 'Level 1 - Level 2 - Level 3 - Level 4 - Level 5 - Level 6 - Level 7'

I would like to get the Level 6 word and level 5 word apart. I did try to google a few things, but they are not really helping me in this case. Because the levels will have different words and such, so it will be different lengths.

I tried the following but I know this will only select the first word between these: ‘-‘ ‘-‘

DECLARE @text VARCHAR(MAX)
SET @text = 'Level 1 - Level 2 - Level 3 - Level 4 - Level 5 - Level 6 - Level 7'
SELECT SUBSTRING(@text,CHARINDEX('-',@text)+1, CHARINDEX('-',@text,CHARINDEX('-',@text)+1) -CHARINDEX('-',@text)-1) as Level 6

Can anyone of you help me on the right path ?

I am currently still new to using CHARINDEX.

Answer

You can try this below logic-

DEMO HERE

DECLARE @text VARCHAR(MAX)
DECLARE @L5 INT
DECLARE @L6 INT

SET @text = 'Level 1 - Level 2 - Level 3 - Level 4 - Level 5 - Level 6 - Level 7'

SELECT 
@L5 = CHARINDEX('-',@text,CHARINDEX('-',@text,CHARINDEX('-',@text,CHARINDEX('-',@text,1)+1)+1)+1),
@L6 = CHARINDEX('-',@text,CHARINDEX('-',@text,CHARINDEX('-',@text,CHARINDEX('-',@text,CHARINDEX('-',@text,1)+1)+1)+1)+1)

SELECT @L5,@l6,
LEFT(RIGHT(@text,LEN(@text)-@L5),CHARINDEX('-',RIGHT(@text,LEN(@text)-@L5))-1),
LEFT(RIGHT(@text,LEN(@text)-@L6),CHARINDEX('-',RIGHT(@text,LEN(@text)-@L6))-1)