Get all the characters after the 3rd hypen (-)

Question

I have column TrackNo with values like

8070444981-010023-013123-INBBTC-C
601724-072923-078923-INAAAX-B

I need to get the values from the 3rd hypen example

-INBBTC-C
-INAAAX-B

I tried substring,charindex but it is not working for both the scenarios

select substring( Trackno,
charindex('-', TrackNo , (charindex('-', TrackNo , 1))
+charindex('-', TrackNo, (charindex('-', TrackNo , 1))+1)),20) 
from table

Answer 1

Please try the following solution based on XML and XQuery.

The XPath predicate /root/r[position() ge 4] is doing the job for your scenario:

Get all the characters after the 3rd hypen (-)

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Trackno VARCHAR(100));  
INSERT INTO @tbl (Trackno) VALUES 
('8070444981-010023-013123-INBBTC-C'),
('601724-072923-078923-INAAAX-B');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = '-';

SELECT t.* 
    , REPLACE(c.query('data(/root/r[position() ge 4])')
        .value('text()[1]', 'VARCHAR(100)'), SPACE(1), @separator) AS result
FROM @tbl AS t
    CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
        REPLACE(Trackno, @separator, ']]></r><r><![CDATA[') + 
        ']]></r></root>' AS XML)) AS t1(c);

Output

ID	Trackno	result
1	8070444981-010023-013123-INBBTC-C	INBBTC-C
2	601724-072923-078923-INAAAX-B	INAAAX-B

Answer 2

If you have SQL 2022, you can use string_split with the optional ordinal argument, and grab whichever pieces you want (after splitting on -).

Barring that, you can brute force the piece with nested charindex calls, like you've done. You just need to make sure you're offsetting each chunk correctly so you're not slicing in the wrong place.

;with trackingNumbers (TrackNo) as
(
    select '8070444981-010023-013123-INBBTC-C'
    union all select '601724-072923-078923-INAAAX-B'
), b as
(
    select
        TrackNo,
        FirstIndex = charindex('-', TrackNo),
        SecondIndex = charindex
            (
                '-', 
                TrackNo, 
                charindex
                (
                    '-', 
                    TrackNo
                ) + 1
            ),
        ThirdIndex = charindex
            (
                '-', 
                TrackNo, 
                charindex
                (
                    '-', 
                    TrackNo, 
                    charindex
                    (
                        '-', 
                        TrackNo
                    ) + 1
                ) + 1
            )
    from trackingNumbers
)
select *,
LastTwo = substring(TrackNo, ThirdIndex + 1, 8000)
from b

There are other ways you can do it by replacing various values in your string to coax it into the form of JSON or XML data if you want, but I'll avoid those in the interest of addressing your original attempted approach.

EDIT Just as a bonus, here's how you could do it with successive cross applies, if you want to go that route.

;with trackingNumbers (TrackNo) as
(
    select '8070444981-010023-013123-INBBTC-C'
    union all select '601724-072923-078923-INAAAX-B'
)
select 
    LastTwo = substring(TrackNo, d.ThirdIndex + 1, 8000),
    FirstIndex,
    SecondIndex,
    ThirdIndex
from trackingNumbers a
cross apply
(
    select FirstIndex = charindex('-', TrackNo)
) b
cross apply
(
    select SecondIndex = charindex('-', TrackNo, b.FirstIndex + 1)
) c
cross apply
(
    select ThirdIndex = charindex('-', TrackNo, c.SecondIndex + 1)
) d

Answer 3

with [table] (Trackno) as (
select '8070444981-010023-013123-INBBTC-C' union
select '601724-072923-078923-INAAAX-B')

select substring(Trackno,
            0 + charIndex('-', TrackNo,
            1 + charIndex('-', TrackNo,
            2 + charIndex('-', Trackno)))
            , 20) as [Result]
from [table]

db<>fiddle