SQL – Getting Index of delimiter found in the variable and substring based on the index

I need to get the last occurrence of symbol found in multiple same symbol.

SELECT Charindex('|', 'abc||xyz')

This will result 4, but i need to get the index of the last ‘|’ which is index of 5. Then substring the string based on that index.

EDIT: I’m sorry I am wrong with my question earlier. I forgot to mention that the string may contains more number of characters and symbols.

@forpas answer is correct if the string ‘abc||xyz’.

But this will become wrong if the string is ‘abc||xyz|||qwe’

EDIT 2: Basically I want to substring the string including the delimiter while looping the string.

Declare 
    @txt as nvarcar(max), 
    @newTxt as nvarchar(50)
SET @txt = 'dog|cat|||fish|horse||||frog'

--while looping

--1st loop:
@newTxt = 'dog|'
@txt = 'cat|||fish|horse||||frog'

--2nd loop:
@newTxt = 'cat|||'
@txt = 'fish|horse||||frog'

--3rd loop:
@newTxt = 'fish|'
@txt = 'horse||||frog'
...

UPDATE The original question is about finding index in the given string. But due to several updates because of the lacking information, the other answer conflicts with the new question. After the 2nd edition, the whole process what I was doing suddenly completed by the accepted answer. So the title of the question and some context are updated for this thread to be useful.

Answer

You can use this SplitString function to get the list of values with the index they where found and adapt it to your purpose….

CREATE FUNCTION [dbo].[fn_SplitString2] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(id int not null primary key identity, splitdata NVARCHAR(MAX), startindex int) 

BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata, startindex)  
        VALUES(SUBSTRING(@string, @start, @end - @start), @start) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END



DECLARE @st VARCHAR(100) = 'dog|cat|||fish|horse||||frog';

SELECT *
FROM [dbo].[fn_SplitString2](@st, '|') A
WHERE splitdata <> ''

OUTPUT:

id splitdata startindex

• 1 dog 1

• 2 cat 5

• 5 fish 11

• 6 horse 16

• 10 frog 25