Looking for the first underscore then find the 5th space after

Question

I am trying to create a filter where I am looking for the first (or last) occurrence of an underscore (or it can be any character) and then start from there to look for the 5th character.

I am thinking of something along the lines of either right or left char index picking a side to start on. Really trying to look for a good explanation of why your answer is written in that manner.

Example: I am looking for __poptarts_________.

So I would want it to start at the leftmost _ and search for the 5th character after that (p).

Answer 1

You could achieve that by using both SUBSTRING and CHARINDEX

SELECT SUBSTRING (string,(CHARINDEX('_',string,0)+1),5)

In your case which would be:

 SELECT SUBSTRING ('I am looking for __poptarts_________',(CHARINDEX('_','I am looking for __poptarts_________',0)+1),5)

Result is _popt because you put two '_' before 'p'