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