sqlsql-serversubstringcharindex

Negative indexing with charindex() and substring function


I have a column that holds the data in the following format:

Field Name
123_456_ABC_DEF
12_34_456_XYZ_PQR
LMN_OPQ_123_456

In each case I require, the last two block of data i.e.

ABC_DEF
XYZ_PQR
123_456

Is there a way to use charindex() in manner where it counts '_' from the right side of the string?


Solution

  • Here's an unreadable & slightly mad way of doing it :-)

    -- DDL and sample data population, start
    DECLARE @tbl TABLE (tokens VARCHAR(256));
    INSERT @tbl VALUES
    ('123_456_ABC_DEF'),
    ('12_34_456_XYZ_PQR'),
    ('LMN_OPQ_123_456');
    
    SELECT REVERSE(LEFT(REVERSE(tokens),CHARINDEX('_',REVERSE(tokens),CHARINDEX('_',REVERSE(tokens))+1)-1))
    FROM @tbl
    

    Basically reversing the text, searching forwards & reversing it back at the end....(SQL Server T-SQL)