1

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.

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
Wimpie Norman
  • 27
  • 5
  • 1
    You should strive to stop using delimited strings altogether. Read [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutely yes!** – Zohar Peled Apr 07 '20 at 09:30

1 Answers1

0

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)
mkRabbani
  • 16,295
  • 2
  • 15
  • 24